Delete ANY blank rows on worksheet

[Guest]

Is it possible either with or without code, to delete all the blank rows in a
worksheet.

I have a worksheet with data occupying 46000 rows and need to delete all
blank rows in between.

I am not a VB developer so if the solution is code then I will need specific
instructions on how to run it, where to put it etc.

I am using excel v2000.

Any help will be greatly appreciated.

Thanks

Anita

 

[Dave O]

There are a number of ways to do this: this is one I've used
successfully.

1. Insert a new column on the extreme left of the data, and data-fill
it consecutively from 1 to 46000.
2. Sort your data on any appropriate column. All the blank rows will
appear either before or after the sorted data. Delete those rows (they
have numbers from step 1 in column A).
3. Sort again on the numbers you entered in column A to put your data
back into it's original order.

Vwalla!

 

[Guest]

Here is a macro to try:


Sub gsnu()
Dim j As Long
Dim i As Long
Dim r As Range
Dim r2 As Range
Set r2 = ActiveSheet.UsedRange

j = r2.Rows.Count + r2.Row - 1
For i = 1 To j
If Application.CountA(Rows(i)) = 0 Then
If r Is Nothing Then
Set r = Rows(i)
Else
Set r = Union(r, Rows(i))
End If
End If
Next i

If Not r Is Nothing Then
r.EntireRow.Delete
End If
End Sub

to use the macro see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[stevebriz]

ok here is another way

FIRST back up back up you spread sheet.
then
Go to View>Toolbars> control toolbox.
Move you cursor over the top of the control toolbox until you see
"commandbutton"
Click on this and then click on you spreadsheet where you want to place
it( it does not really matter where you place the command button on the
spread sheet.)
then double click it and the VB editor will open

paste the code below between the Private Sub CommandButton1_Click() and
the End Sub.
Press F5 and the code will run and do its thing. PLEASE:Remember if
you do this- IT CAN NOT BE UNDONE. this is not reversible.
go back to you spread sheet and all the blank rows should be gone.
You can then delete the button you added if you want (double clicking
on the button reruns the code) To Delete the button- click once and
press Delete.

If you want to go back to where the code is press ALT & F11
you can delect all the code if you like too ( now that you finished
with it) To do this Highlight all and hit delete.
Close Vb editor and then you can save your work book
Done

Hope this helps
'-------------------------------------------------------
'Deletes the entire row within the selection if the ENTIRE row contains
no data.

'We use Long in case they have over 32,767 rows selected.
Dim i As Long

'We turn off calculation and screenupdating to speed up the macro.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

'We work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

'-------------------------------------------------------

 

[Guest]

Fantastic -m it worked -thanks !

 

[Gord Dibben]

Select a column and F5>Special>Blanks>OK

Edit>Delete>Entire Row.


Gord Dibben MS Excel MVP

 

[Francois via OfficeKB.com]

Select a column and F5>Special>Blanks>OK

Edit>Delete>Entire Row.

Gord Dibben MS Excel MVP

Is it possible either with or without code, to delete all the blank rows in a
worksheet.

[quoted text clipped - 12 lines]

Anita

Thanks Gord,

Such an obviously easy method, but it's saved me a lot of time doing a
similar task

 

[Gord Dibben]

Thanks for the feedback.

Sometimes we do tend to over-complicate some operations and write macros or add
helper columns etc. when not needed.


Gord

Select a column and F5>Special>Blanks>OK

Edit>Delete>Entire Row.

Gord Dibben MS Excel MVP

Is it possible either with or without code, to delete all the blank rows in a
worksheet.

[quoted text clipped - 12 lines]

Anita

Thanks Gord,

Such an obviously easy method, but it's saved me a lot of time doing a
similar task

Gord Dibben MS Excel MVP