Removing Blank Rows in an excel spreadsheet

  • Thread starter Thread starter Phyllis
  • Start date Start date
P

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.
 
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..
Click to expand...

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
 
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)
 
Thank you so much! Worked like a charm!

Chip Pearson said:
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)
Click to expand...
 
Select a column with the blank rows.

F5>Special>Blanks>OK

Edit>Delete>Entire Row


Gord Dibben MS Excel MVP
 
Back
Top