finding empty rows and deleting

[Ryan]

I have range of data that varies. The data has empty rows of data. I need to find the empty rows and delete them. Need help creating VBA code to do this.

 

[Doug Glancy]

I must have got this from Google Groups (archived NG postings, check it
out!) or Ron's web site. It deletes all blank rows in a sheet, actually any
row with a blank in column 1, same thing in your case. If you have some
blank rows outside your range of data that you want to keep, you'd have to
tailor.

Public Sub DeleteBlankRows()

'Ron De Bruin

Dim column_with_blanks As Long

column_with_blanks = 1

On Error Resume Next 'In case there are no blank rows

Columns(column_with_blanks).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

End Sub

hth,

Doug Glancy

I have range of data that varies. The data has empty rows of data. I need

to find the empty rows and delete them. Need help creating VBA code to do
this.

 

[Gord Dibben]

Ryan

Delete entire rows based on blanks in your selected column.

Public Sub DeleteRowOnCell()
''delete any row that has a blank in selected column(s)
Set coltocheck = Application.InputBox(prompt:= _
"Select A Column", Type:=8)
' On Error Resume Next
coltocheck.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange
End Sub

Assuming entire row has to be blank.

Sub DeleteEmptyRows()
''only if entire row is blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then Rows(R).Delete
Next R
End Sub

Gord Dibben Excel MVP

 

[Ocmulgee]

I've found that it's faster in Excel to insert a numbered column,
then sort the data column to drive empty rows to bottom of table,
then delete empty rows and rearrange the table back to the original order.
This is faster than walking the table (with code), finding blank rows and
deleting one at a time.

The following assumes your table starts in a1, adjust address values if your
column has header

Sub DeleteEmptyRows()
'insert new column 'a' and number it sequentially for sorting
Columns("A:A").Insert Shift:=xlToRight
Range("A1").FormulaR1C1 = "1"
Range("A2").FormulaR1C1 = "2"
Range("A3").FormulaR1C1 = "3"
Set StartRange1 = Range("a3")
Set EndRange1 = Range("B65536").End(xlUp).Offset(0, -1)
Range(StartRange1, EndRange1).DataSeries Rowcol:=xlColumns,
Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
'sort by column 'B' to drive empty rows to bottom of column
Range("B1").Sort Key1:=Range("B1"), Order1:=xlAscending,
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
'delete empty rows
Set StartRange2 = Range("b1").End(xlDown).Offset(1, -1)
Set EndRange2 = Range("A65536").End(xlUp)
Range(StartRange2, EndRange2).EntireRow.Delete
'sort again by column 'A' to restore original order
Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
'delete column 'A'
Columns("A:A").Delete
End Sub

hope this helps

I have range of data that varies. The data has empty rows of data. I need

to find the empty rows and delete them. Need help creating VBA code to do
this.