How do I remove rows that are empty

[greaseman]

I have several Excel files I received from a client. In each of these files, every other line is blank. How might I go about removing these blank lines and then save the file, using VBA? I've searched on this forum and could not locate what I need to know.

Any help is truly appreciated. Thanks!

[Ron de Bruin]

One way is to use SpecialCells
This macro test column A for blanks

Sub DeleteBlankRows_2()
'This macro delete all rows with a blank cell in column A
'If there are no blanks or there are too many areas you see a MsgBox
Dim CCount As Long
On Error Resume Next

With Columns("A") ' You can also use a range like this Range("A1:A8000")

CCount = .SpecialCells(xlCellTypeBlanks).Areas(1).Cells.Count

If CCount = 0 Then
MsgBox "There are no blank cells"
ElseIf CCount = .Cells.Count Then
MsgBox "There are more then 8192 areas"
Else
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If

End With

On Error GoTo 0
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"greaseman" <greaseman.262a0a_1144694112.0885@excelforum-nospam.com> wrote in message
news:greaseman.262a0a_1144694112.0885@excelforum-nospam.com...
>
> I have several Excel files I received from a client. In each of these
> files, every other line is blank. How might I go about removing these
> blank lines and then save the file, using VBA? I've searched on this
> forum and could not locate what I need to know.
>
> Any help is truly appreciated. Thanks!
>
>
> --
> greaseman
> ------------------------------------------------------------------------
> greaseman's Profile: http://www.excelforum.com/member.php...o&userid=28808
> View this thread: http://www.excelforum.com/showthread...hreadid=531616
>

[Bob Phillips]

For i = Cells(Rows.Count,"A").End(xlUp).Row To 1 Step -1
If Cells(i,"A").Value = "" Then
Rows(i).Delete
End If
Next i

Activeworkbook.Save

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"greaseman" <greaseman.262a0a_1144694112.0885@excelforum-nospam.com> wrote
in message news:greaseman.262a0a_1144694112.0885@excelforum-nospam.com...
>
> I have several Excel files I received from a client. In each of these
> files, every other line is blank. How might I go about removing these
> blank lines and then save the file, using VBA? I've searched on this
> forum and could not locate what I need to know.
>
> Any help is truly appreciated. Thanks!
>
>
> --
> greaseman
> ------------------------------------------------------------------------
> greaseman's Profile:
http://www.excelforum.com/member.php...o&userid=28808
> View this thread: http://www.excelforum.com/showthread...hreadid=531616
>

[greaseman]

Thank you both for your very quick replies!! I sure appreciate it! I'll try both suggestions and see what comes out of it!

Nice to have forums that are so helpfull.

[Gary L Brown]

This macro will delete the row ONLY if ALL cells in the used range of the row
are blank.

'/==============================================/
' Sub Purpose: Delect all blank ROWS within the active cell's
' Used Range
'
Public Sub DeleteBlankRows()
Dim dbMaxRow As Double, dbMinRow As Double, i As Double
Dim dbMaxCol As Double
Dim rng As Range

On Error Resume Next

'only look in used area of the worksheet where active cell is
Set rng = Selection.Parent.UsedRange

'calculate area to be searched for blank rows
dbMaxRow = rng.Rows.Count '# of rows in used area
dbMinRow = rng.Cells(1, 1).Row '1st row in used area
dbMaxCol = rng.EntireColumn.Count '# of columns in used area

For i = dbMaxRow To dbMinRow Step -1
If IsError(rng.Cells(1, 1).Offset(i - 1, 0).EntireRow. _
SpecialCells(xlCellTypeBlanks).Count) Then
Else
If rng.Cells(1, 1).Offset(i - 1, 0).EntireRow. _
SpecialCells(xlCellTypeBlanks).Count = dbMaxCol Then
rng.Cells(1, 1).Offset(i - 1, 0).EntireRow.Delete
End If
End If
Next i

Set rng = Nothing

End Sub
'/==============================================/


HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"greaseman" wrote:

>
> I have several Excel files I received from a client. In each of these
> files, every other line is blank. How might I go about removing these
> blank lines and then save the file, using VBA? I've searched on this
> forum and could not locate what I need to know.
>
> Any help is truly appreciated. Thanks!
>
>
> --
> greaseman
> ------------------------------------------------------------------------
> greaseman's Profile: http://www.excelforum.com/member.php...o&userid=28808
> View this thread: http://www.excelforum.com/showthread...hreadid=531616
>
>

[greaseman]

Thank you all......Bob, I used your approach, since it was the least coding, and especially where I already know that every other row will always be empty.

I appreciate everyone's help very much.

[Ron de Bruin]

Hi greaseman

Bob's example have less rows but is slower then the code I posted<g>
If you use Bob's example and you have a lot of rows to check you must add code to
make it run faster.

See this page for examples if you are interested
http://www.rondebruin.nl/delete.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"greaseman" <greaseman.262fkc_1144701302.5542@excelforum-nospam.com> wrote in message
news:greaseman.262fkc_1144701302.5542@excelforum-nospam.com...
>
> Thank you all......Bob, I used your approach, since it was the least
> coding, and especially where I already know that every other row will
> always be empty.
>
> I appreciate everyone's help very much.
>
>
> --
> greaseman
> ------------------------------------------------------------------------
> greaseman's Profile: http://www.excelforum.com/member.php...o&userid=28808
> View this thread: http://www.excelforum.com/showthread...hreadid=531616
>