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

Any help is truly appreciated. Thanks

--
greasema
-----------------------------------------------------------------------
greaseman's Profile: http://www.excelforum.com/member.php...fo&userid=2880
View this thread: http://www.excelforum.com/showthread.php?threadid=53161

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.Cou nt

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" wrote in message
...

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

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" wrote
in message ...

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

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.


--
greaseman
------------------------------------------------------------------------
greaseman's Profile: http://www.excelforum.com/member.php...o&userid=28808
View this thread: http://www.excelforum.com/showthread...hreadid=531616

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

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

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

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" wrote in message
...

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