Hi all

I am complete novice when it come to macros in excel but I am trying t
learn! I have been trying to get a loop macro to work to delete cell
which are blank. But it just keeps deleting and does not stop whe
there is some content in the cell.
HEre is the code! What am I doing wrong?

Sub Macro2()

Do
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Loop Until a1 0

End Su

--
jim2

-----------------------------------------------------------------------
jim27's Profile: http://www.excelforum.com/member.php...fo&userid=1537
View this thread: http://www.excelforum.com/showthread.php?threadid=26996

You need a few things

- identify the last cell
- keep a counter/pointer, so that you know where you are
- work bottom up (when deleting)
- iden tify a key column (I use A in the example)

Sub Macro2()
Dim cRows As Long
Dim i As Long

'find the row number of the last row that has data in it in
'column A
cRows = Cells(Rows.Count,"A").End(xlUp).Row
'now run a loop from that line back to row 1
For i = cRows To 1 Step -1
'check if the cell for this row in column is empty
If Cells(i,"A").Value = "" Then
Cells(i,"A").Delete Shift:=xlUp
End If
Next

End Sub


--

HTH

RP

"jim27" wrote in message
...

Hi all

I am complete novice when it come to macros in excel but I am trying to
learn! I have been trying to get a loop macro to work to delete cells
which are blank. But it just keeps deleting and does not stop when
there is some content in the cell.
HEre is the code! What am I doing wrong?

Sub Macro2()

Do
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Loop Until a1 0

End Sub


--
jim27


------------------------------------------------------------------------
jim27's Profile:
http://www.excelforum.com/member.php...o&userid=15379
View this thread: http://www.excelforum.com/showthread...hreadid=269967

Just to add some explanation to you current code.
a1 doesn't refer to Range("A1")

Sub Macro2()

Do While isempty(Range("a1"))
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Loop

End Sub

--
Regards,
Tom Ogilvy


"jim27" wrote in message
...

Hi all

I am complete novice when it come to macros in excel but I am trying to
learn! I have been trying to get a loop macro to work to delete cells
which are blank. But it just keeps deleting and does not stop when
there is some content in the cell.
HEre is the code! What am I doing wrong?

Sub Macro2()

Do
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Loop Until a1 0

End Sub


--
jim27


------------------------------------------------------------------------
jim27's Profile:
http://www.excelforum.com/member.php...o&userid=15379
View this thread: http://www.excelforum.com/showthread...hreadid=269967