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" <(E-Mail Removed)> wrote in message
news:F3B60F96-A0C5-4C62-A843-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:F3B60F96-A0C5-4C62-A843-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
news:F05E51AF-1B59-4504-8693-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:F3B60F96-A0C5-4C62-A843-(E-Mail Removed)...
>> >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" <(E-Mail Removed)> wrote in message
news:F3B60F96-A0C5-4C62-A843-(E-Mail Removed)...
>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