Hi

I have a large sheet and need a macro to delete any row which does not
have a value in column B. So, if B32 was blank, the whole of row 32
would be deleted. Can anyone come up with a simple macro to do this?

Thanks,

Bertie


--
claytorm
------------------------------------------------------------------------
claytorm's Profile: http://www.excelforum.com/member.php...o&userid=11610
View this thread: http://www.excelforum.com/showthread...hreadid=483223

an easy way is to sort and delete desired or datafilterautofilter

--
Don Guillett
SalesAid Software

"claytorm" wrote in
message ...

Hi

I have a large sheet and need a macro to delete any row which does not
have a value in column B. So, if B32 was blank, the whole of row 32
would be deleted. Can anyone come up with a simple macro to do this?

Thanks,

Bertie


--
claytorm
------------------------------------------------------------------------
claytorm's Profile:
http://www.excelforum.com/member.php...o&userid=11610
View this thread: http://www.excelforum.com/showthread...hreadid=483223

Try:

Sub rowremover()
Dim i As Long
For i = 65536 To 1 Step -1
If IsEmpty(Cells(i, 2)) Then
Rows(i).Select
Selection.Delete Shift:=xlUp
End If
Next
End Sub

Without VBA, just sort on column B and discard the blanks.
--


Gary's Student


"claytorm" wrote:


Hi

I have a large sheet and need a macro to delete any row which does not
have a value in column B. So, if B32 was blank, the whole of row 32
would be deleted. Can anyone come up with a simple macro to do this?

Thanks,

Bertie


--
claytorm
------------------------------------------------------------------------
claytorm's Profile: http://www.excelforum.com/member.php...o&userid=11610
View this thread: http://www.excelforum.com/showthread...hreadid=483223

why use all rows and why select?

For i = cells(rows.count,"b").end(xlup).row To 2 Step -1
If IsEmpty(Cells(i, 2)) Then Rows(i).Delete
Next

--
Don Guillett
SalesAid Software

"Gary''s Student" wrote in message
...
Try:

Sub rowremover()
Dim i As Long
For i = 65536 To 1 Step -1
If IsEmpty(Cells(i, 2)) Then
Rows(i).Select
Selection.Delete Shift:=xlUp
End If
Next
End Sub

Without VBA, just sort on column B and discard the blanks.
--


Gary's Student


"claytorm" wrote:


Hi

I have a large sheet and need a macro to delete any row which does not
have a value in column B. So, if B32 was blank, the whole of row 32
would be deleted. Can anyone come up with a simple macro to do this?

Thanks,

Bertie


--
claytorm
------------------------------------------------------------------------
claytorm's Profile:
http://www.excelforum.com/member.php...o&userid=11610
View this thread:
http://www.excelforum.com/showthread...hreadid=483223

Hi claytorm

If they are really blank and no more then 8192 separate areas ?

With ActiveSheet
On Error Resume Next
.Columns("B").SpecialCells(xlCellTypeBlanks).Entir eRow.Delete
On Error GoTo 0
End With

Or other ways
http://www.rondebruin.nl/delete.htm


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


"claytorm" wrote in message
...

Hi

I have a large sheet and need a macro to delete any row which does not
have a value in column B. So, if B32 was blank, the whole of row 32
would be deleted. Can anyone come up with a simple macro to do this?

Thanks,

Bertie


--
claytorm
------------------------------------------------------------------------
claytorm's Profile: http://www.excelforum.com/member.php...o&userid=11610
View this thread: http://www.excelforum.com/showthread...hreadid=483223