-
-
@אהרן
Excel VBA - Delete empty rows
Ask Question11
6
I would like to delete the empty rows my ERP Quotation generates. I'm trying to go through the document (A1:Z50) and for each row where there is no data in the cells (A1-B1...Z1 = empty, A5-B5...Z5 = empty) I want to delete them.I found this, but can't seem to configure it for me.
On Error Resume Next
Worksheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
vba excel-vba excel
shareedit
edited Jul 9 '18 at 19:34Community
11
asked Feb 21 '12 at 14:55CustomX
6,2882768106
1
You have tried substituting A:A with C:C, have you? – GSerg Feb 21 '12 at 14:59
Serg, it seems I was wrong explaining my problem. I need to check an entire row (A1-Z1) to see whether it's empty or not, and this down to A50-Z50. – CustomX Feb 21 '12 at 15:03
So a "whole row" for your purposes is columns A-Z and not like AA...ZZ...? – Brad Feb 21 '12 at 15:10
Yes, column A1 to Z1 is an entire row for me. If there aren't any items in any of these cells (A1 to Z1) then the row is empty and can be deleted. – CustomX Feb 21 '12 at 15:12
add a comment
6 Answers
active oldest votes19
How about
sub foo()
dim r As Range, rows As Long, i As Long
Set r = ActiveSheet.Range("A1:Z50")
rows = r.rows.Count
For i = rows To 1 Step (-1)
If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).Delete
Next
End Sub
shareedit
answered Feb 21 '12 at 15:15Alex K.
139k21202239
1
Works like a charm! – CustomX Feb 21 '12 at 15:21
1
Wasn't too bad, just bit too long for my needs atm. Someone else who might have the same problem will be helped with your example though
– CustomX Feb 21 '12 at 15:41
add a comment11
Try this
Option Explicit
Sub Sample()
Dim i As Long
Dim DelRange As RangeOn Error GoTo Whoa Application.ScreenUpdating = False For i = 1 To 50 If Application.WorksheetFunction.CountA(Range("A" & i & ":" & "Z" & i)) = 0 Then If DelRange Is Nothing Then Set DelRange = Range("A" & i & ":" & "Z" & i) Else Set DelRange = Union(DelRange, Range("A" & i & ":" & "Z" & i)) End If End If Next i If Not DelRange Is Nothing Then DelRange.Delete shift:=xlUpLetsContinue:
Application.ScreenUpdating = TrueExit SubWhoa:
MsgBox Err.Description
Resume LetsContinue
End Sub
IF you want to delete the entire row then use this codeOption Explicit
Sub Sample()
Dim i As Long
Dim DelRange As RangeOn Error GoTo Whoa Application.ScreenUpdating = False For i = 1 To 50 If Application.WorksheetFunction.CountA(Range("A" & i & ":" & "Z" & i)) = 0 Then If DelRange Is Nothing Then Set DelRange = Rows(i) Else Set DelRange = Union(DelRange, Rows(i)) End If End If Next i If Not DelRange Is Nothing Then DelRange.Delete shift:=xlUpLetsContinue:
Application.ScreenUpdating = TrueExit SubWhoa:
MsgBox Err.Description
Resume LetsContinue
End Sub
shareedit
answered Feb 21 '12 at 15:13Siddharth Rout
115k14153205
Thank you for your effort, not sure whether it works, but it's a bit longer than the snippet I accepted. – CustomX Feb 21 '12 at 15:22
@Tom: It is longer because I have used the right way to code with all error handling. Anyways leave it – Siddharth Rout Feb 21 '12 at 15:25
Oh sorry
I only needed a quick fix though. Thanks for the thorough example though – CustomX Feb 21 '12 at 15:40
add a comment2
I know I am late to the party, but here is some code I wrote/use to do the job.
Sub DeleteERows()
Sheets("Sheet1").Select
Range("a2:A15000").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
shareedit
edited Dec 12 '18 at 21:55Kingsley
2,71221225
answered Dec 12 '18 at 21:45Smiley Lando
464
add a comment0
This worked great for me (you can adjust lastrow and lastcol as needed):
Sub delete_rows_blank2()
t = 1
lastrow = ActiveSheet.UsedRange.Rows.Count
lastcol = ActiveSheet.UsedRange.Columns.CountDo Until t = lastrow
For j = 1 To lastcol
'This only checks the first column because the "Else" statement below will skip to the next row if the first column has content.
If Cells(t, j) = "" Thenj = j + 1 If j = lastcol Then Rows(t).Delete t = t + 1 End If Else 'Note that doing this row skip, may prevent user from checking other columns for blanks. t = t + 1 End IfNext
Loop
End Sub
shareedit
edited Jan 17 at 0:39Mike
134
answered Feb 27 '18 at 15:10IRENE G
31014
add a comment0
In order to have the On Error Resume function work you must declare the workbook and worksheet values as such
On Error Resume Next
ActiveWorkbook.Worksheets("Sheet Name").Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
I had the same issue and this eliminated all the empty rows without the need to implement a For loop.shareedit
answered Apr 30 '18 at 14:39Jerome
1
add a comment0
To make Alex K's answer slightly more dynamic you could use the code below:
Sub DeleteBlankRows()
Dim wks As Worksheet
Dim lngLastRow As Long, lngLastCol As Long, lngIdx As Long, _
lngColCounter As Long
Dim blnAllBlank As Boolean
Dim UserInputSheet As StringUserInputSheet = Application.InputBox("Enter the name of the sheet which you wish to remove empty rows from")
Set wks = Worksheets(UserInputSheet)
With wks
'Now that our sheet is defined, we'll find the last row and last column
lngLastRow = .Cells.Find(What:="", LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
lngLastCol = .Cells.Find(What:="", LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column'Since we need to delete rows, we start from the bottom and move up For lngIdx = lngLastRow To 1 Step -1 'Start by setting a flag to immediately stop checking 'if a cell is NOT blank and initializing the column counter blnAllBlank = True lngColCounter = 2 'Check cells from left to right while the flag is True 'and the we are within the farthest-right column While blnAllBlank And lngColCounter <= lngLastCol 'If the cell is NOT blank, trip the flag and exit the loop If .Cells(lngIdx, lngColCounter) <> "" Then blnAllBlank = False Else lngColCounter = lngColCounter + 1 End If Wend 'Delete the row if the blnBlank variable is True If blnAllBlank Then .rows(lngIdx).delete End If Next lngIdxEnd With
MsgBox "Blank rows have been deleted."
End Sub
This was sourced from this website and then slightly adapted to allow the user to choose which worksheet they want to empty rows removed from.shareedit
-
@אהרן אמר באיך מוחקים שורות ריקות באקסל:
מה הפונקציה (כנ"ל למחוק את כל השורה) התנאי שתא בטור מסוים ריק?
בדוגמא ש clickone הביא,
תביא ל range רק את העמודה הרצויה.עריכה:
תצטרך לתת לו 2 טווחים, כי הוא מוחק רק את השורות של הטווח, לא את כל השורה.Sub foo() Dim r As Range, rb As Range, rows As Long, i As Long Set r = ActiveSheet.Range("B1:B50") Set rb = ActiveSheet.Range("a1:z50") rows = r.rows.Count For i = rows To 1 Step (-1) If WorksheetFunction.CountA(r.rows(i)) = 0 Then rb.rows(i).Delete Next End Subאו במקום 2 טווחים, לבחור את כל השורה
If WorksheetFunction.CountA(r.rows(i)) = 0 Then ' rb.rows(i).Delete rb.rows(CStr(i) & ":" & CStr(i)).Select Selection.Delete End If -
@דוב אמר באיך מוחקים שורות ריקות באקסל:
מי עוזר לבור כמוני? איפה כותבים את הקודים האלו?
https://support.office.com/he-il/article/יצירת-פונקציות-מותאמות-אישית-ב-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f
https://sites.google.com/site/studyexcel1/hishuvim/function/motameshi -
D dovid העביר נושא זה מ-תכנות ב-
