Blank Rows

  • Thread starter Thread starter jeruddy
  • Start date Start date
J

jeruddy

I was reading about deleting blank rows but they aren't really
addressing the problem that I am having.

I pull info from an outside program and insert it into an excel sheet
to reformat it. What I need to do more efficiently is get rid of the
blank rows across the columns, BUT not delete rows that have an info
in them. For Instance...

Columns A through G have info on the first row.
Columns D through G have info on the second and third row
Column G has info in the 4th.

Between the three rows there are blank rows that I don't need and the
other thing is I have about 17 pages of the same dilemma so it isn't
just one set and one other thing is I run this report every other week
or so and I have another that I am running weekly that can range from
5 pages to the most recent one which was 32. There are no calculations
anywhere in the spreadsheet so deleting rows shouldn't be a problem.

I am new in this position and have no one to ask about this type of
problem. If I can cut this process time down it would be very
impressive and make my life easier.

Thanks!!!
 
Try this macro which will delete any row that is completely blank.

Sub DeleteEmptyRows()
'only if entire row is blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).Delete
End If
Next r
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

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

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP
 
Try this macro which will delete any row that is completelyblank.

Sub DeleteEmptyRows()
'only if entire row isblank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).Delete
End If
Next r
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

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

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.

Gord Dibben MS Excel MVP
Click to expand...

I will definitely try this!! BUT I found another little thing that
could be a time-saver if there was a way to do it. After separating
types of product so that I can see what I have and don't have, I put
each list into a particular order. What I need to do with this is
insert a row, fill it with black, and resize it to 3pt. This isn't
the same one as what I asked about before. I have written a macro to
do all the basic formatting but this action doesn't seem to want to
work (in a separate macro.) because it wants to insert the row in the
same row. I really would like just to be able do a shortcut to
accomplish this instead of inserting, coloring, rezising then on to
the next one to do exactly the same thing.

Thanks again!!!
 
What would distinguish the end of one set from the next set?

This macro looks at Column A and at every change in value inserts a row colored
black and set to 3pt.

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) <> Cells(i, 1) Then _
Cells(i, 1).Resize(1, 1).EntireRow.Insert
With Rows(i)
.RowHeight = 3
.Interior.ColorIndex = 1
End With

Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord
 
Back
Top