I want to delete empty rows in a data list by macro.

Which is the simplest method of doing this


--
Charlie New XP User

One way is to sort the list. Empty rows end up at the top or bottom
depending on which way you sort.

--

Rod Gill


"sswcharlie1" wrote in message
...
I want to delete empty rows in a data list by macro.

Which is the simplest method of doing this


--
Charlie New XP User

Unfortunately the data is in groups and I would mix up the data that I want
to extract by a macro.

Charlie
--
Charlie New XP User


"Rod Gill" wrote:

One way is to sort the list. Empty rows end up at the top or bottom
depending on which way you sort.

--

Rod Gill


"sswcharlie1" wrote in message
...
I want to delete empty rows in a data list by macro.

Which is the simplest method of doing this


--
Charlie New XP User

Have a look here,
Think it may assist you:

http://www.ozgrid.com/VBA/VBACode.htm

Corey....
"sswcharlie1" wrote in message
...
Unfortunately the data is in groups and I would mix up the data that I
want
to extract by a macro.

Charlie
--
Charlie New XP User


"Rod Gill" wrote:

One way is to sort the list. Empty rows end up at the top or bottom
depending on which way you sort.

--

Rod Gill


"sswcharlie1" wrote in message
...
I want to delete empty rows in a data list by macro.

Which is the simplest method of doing this


--
Charlie New XP User

The following uses column A to determine the depth of the data, and scan all
rows from the bottom to top, removing rows where every column in that row is
empty. If cells contain spaces then this will not work. So use version 2
below.

Sub exempty()
Dim xlr As Long, xr As Long
xlr = Cells(Rows.Count, 1).End(xlUp).Row
For xr = xlr To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(xr)) = 0 Then
Rows(xr).EntireRow.Delete
Next xr
End Sub

' code if cells might contain spaces which should be ignored
Sub exempty2()
Dim xlr As Long, xr As Long, xlDelete As Boolean, xc As Integer
xlr = Cells(Rows.Count, 1).End(xlUp).Row
For xr = xlr To 1 Step -1
xlDelete = True
For xc = 1 To 256
If Len(Trim(Cells(xr, xc))) 0 Then
xlDelete = False
Exit For
End If
Next xc
If xlDelete Then Rows(xr).EntireRow.Delete
Next xr
End Sub

--
Cheers
Nigel



"sswcharlie1" wrote in message
...
I want to delete empty rows in a data list by macro.

Which is the simplest method of doing this


--
Charlie New XP User

"sswcharlie1" wrote:

I want to delete empty rows in a data list by macro.
Sub cleanline()
Dim rg As Range, rgBlank As Range

Set rg = ActiveSheet.Range("A:A")

On Error Resume Next
Set rgBlank = rg.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rgBlank Is Nothing Then 'no blank cell
MsgBox "No Blank cells found"
Else 'else delete entire rows
rgBlank.EntireRow.Delete
End If
End Sub
Which is the simplest method of doing this


--
Charlie New XP User