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.

On Dec 17, 4:08*pm, Phyllis wrote:
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

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)


On Wed, 17 Dec 2008 14:08:01 -0800, Phyllis
wrote:

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.

Thank you so much! Worked like a charm!

"Chip Pearson" wrote:

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)


On Wed, 17 Dec 2008 14:08:01 -0800, Phyllis
wrote:

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 wish it was that easy, I tried that first, but thank you.

"jlclyde" wrote:

On Dec 17, 4:08 pm, Phyllis wrote:
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

Select a column with the blank rows.

F5SpecialBlanksOK

EditDeleteEntire Row


Gord Dibben MS Excel MVP

On Wed, 17 Dec 2008 14:08:01 -0800, Phyllis
wrote:

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.