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
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
an easy way is to sort and delete desired or data>filter>autofilter
--
Don Guillett
SalesAid Software
[email protected]
"claytorm" <[email protected]> wrote in
message news:[email protected]...
>
> 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
[email protected]
"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> 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).EntireRow.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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks