Removing Blank Rows in an excel spreadsheet

[Phyllis]

I have a spreadsheet that contains blank row to separate data, I need to
remove all the blank rows in order to resort the data for creating labels.

 

[jlclyde]

I have a spreadsheet that contains blank row to separate data, I need to
remove all the blank rows in order to resort the data for creating labels..

Are you asking how to remove the blank rows? cause you can just sort
the sheet and the blank rows will go to the bottom. If you are
wondering how to sort the sheet and then put the rows back in, then
that is a little trickier.

Jay

 

[Chip Pearson]

You can do it with simple code:

Sub RemoveBlankRows()
Dim WS As Worksheet
Dim RNdx As Long
Dim LastRow As Long

Set WS = Worksheets("Sheet1")
With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For RNdx = LastRow To 1 Step -1
If Application.CountA(WS.Rows(RNdx)) = 0 Then
WS.Rows(RNdx).Delete
End If
Next RNdx
End Sub

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Phyllis]

Thank you so much! Worked like a charm!

You can do it with simple code:

Sub RemoveBlankRows()
Dim WS As Worksheet
Dim RNdx As Long
Dim LastRow As Long

Set WS = Worksheets("Sheet1")
With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For RNdx = LastRow To 1 Step -1
If Application.CountA(WS.Rows(RNdx)) = 0 Then
WS.Rows(RNdx).Delete
End If
Next RNdx
End Sub

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Phyllis]

I wish it was that easy, I tried that first, but thank you.

 

[Gord Dibben]

Select a column with the blank rows.

F5>Special>Blanks>OK

Edit>Delete>Entire Row


Gord Dibben MS Excel MVP