excel: delete rows with formula 1

[Mariootje]

Hi there,

I´m having a report wich is generated with a lot of empty rows. For a better view I wonder if there is a formula whicht can delete the empty rows.

First step is to identify the empty row, that is not that big a problem I suppose -> if(a1=" ";the removing part;do nothing)

My questions are:
- what is the code for removing a row;
- how do you define "do nothing" in Excel?

Regards,
Mario

 

[Dreamboat]

Mario, here's a much easier way.

Find a column where the cells are empty ONLY when the entire row is empty. Usually your first column.

Select that whole column by clicking on its letter.

Hit Edit-Goto-Special and choose Blanks.

Hit Edit-Delete and you'll be asked what you want to delete. Choose entire row.

If you really need a macro, you could possibly record this, but you may need to edit it. I didn't test macro-ability.

Hope this helps!

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[DaleWatson123321]

Mario,

Here's another option...

Because you are "generating the report", you probably want to add a routine which "takes the extra step" of removing any BLANK rows.

Based on your example - where you test for a BLANK cell in column "A", the following routine will work. It involves extracting the data to a separate sheet, and (automatically) setting the print range based on the data extracted.

An alternative to "extracting" the data, would be to "filter-in-place", but my preference is the "extraction" because it affords the option to extract "select" data and to "re-arrange" the order of the "fields" if and when this is desired.

Anyway, I hope this provides you with a useful option. If you have any difficulty, you could choose to email me, and I'll send the file by return email.

Please advise as to how useful you find this option.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca


HERE'S THE CODE

 

[Dreamboat]

Uh. Dale? Where's the code? LOL

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[DaleWatson123321]

Thanks, Dreamboat !!! You ALWAYS seem to be "on your toes". Obviously, I'm not this morning. I'm certain, though, that I saw the code in the "Preview". I experienced more than one problem in the past with the "Preview" option, so perhaps it acted up this time ...or it could have been me.

Anyway, THANKS for noticing, and here's the code (WITHOUT using "Preview&quot...

===========
VBA CODE
===========

Dim FirstCell As String
Dim LastCell As String
Dim LastRow As String
Dim LastColumn As String
Dim Print_Range As String
Dim data As Range

Sub Extract() 'extracts NON-BLANK rows to EXTRACTION Sheet
Application.ScreenUpdating = False
Delete_Existing 'deletes existing data from EXTRACTION sheet

'The following "extraction" requires creation of a Range Name
'called "data", with the top row of "data" being the row with
'field names, and the range including the entire range of data
'from which the extraction of non-blank rows is to take place.

'"crit" is another Range Name you need to assign to TWO cells.
'Create this name on the same sheet, off to the side out of the
'wayl. The first cell is to be left BLANK. In the cell below,
'enter the following formula: =A12="" where "A12" refers to the
'first row of data - the row immediately below the field names.
'(Change the reference to "A12" to refer to your first row of data).

'On a separate sheet, copy from the "data" range, those fields
'you want to have included in the report. Then assign the
'Range Name "out" to the row of field names.

Range("data&quot.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:="crit", _
CopyToRange:=Range("out&quot, _
Unique:=False

Application.Goto Reference:="out"
Get_Last_Row 'subroutine to determine last row in use
Set_PrintRange 'sets the print range
'and the extraction range to be copied
Application.ScreenUpdating = True
Application.Goto Reference:="R1C1"
End Sub

Sub Set_PrintRange() 'sets the print range
'and the extraction range to be copied
Application.Goto Reference:="out"
FirstCell = ActiveCell.Address
Get_Last_Row
LastColumn = "F" 'REQUIRES CHANGING based on extraction range
LastCell = LastColumn & LastRow
Print_Range = FirstCell & " : " & LastCell
Range(Print_Range).Name = "PR"
ActiveSheet.PageSetup.PrintArea = "PR"
ActiveSheet.PageSetup.PrintTitleRows = "top"
Range("PR&quot.Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With

' Use the following line at the bottom (remove the " ' &quot
' if you want the report sent automatically to the printer.
' Currently, the print settings are already set, and the
' user ONLY has to use <Control> P <Enter>, or click on
' Excel's &quot;Print Icon&quot; at the top of the screen.

' ActiveWindow.SelectedSheets.PrintOut Copies:=1

End Sub

Sub Get_Last_Row() 'determines the last row in use
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(3, 0).Select
ActiveCell.End(xlToLeft).Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row
End Sub

Sub Delete_Existing() 'deletes existing data from the EXTRACTION sheet
Application.Goto Reference:=&quot;out&quot;
ActiveCell.Offset(1, 0).Select
FirstCell = ActiveCell.Address
Get_Last_Row
ActiveCell.Offset(1, 0).Select 'used in case there is NO data in extraction range
LastRow = ActiveCell.Row
LastColumn = &quot;F&quot; 'REQUIRES CHANGING based on number fields extracted
LastCell = LastColumn & LastRow
Print_Range = FirstCell & &quot; : &quot; & LastCell
Range(Print_Range).Name = &quot;PR&quot;
Range(&quot;PR&quot.Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlNone 'removes existing lines
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlNone 'removes existing lines
End With
Selection.Delete 'deletes existing data from EXTRACTION sheet
End Sub

============
END OF CODE
============

Please advise as to how useful this option is.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Dreamboat]

Dale, you and I often post in the same threads. I generally get to see your posts pretty quickly (you know I have no other life than my tech support sites LOL), so I just often get to see them first.



Happy new year to ya!

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[DaleWatson123321]

THANKS, Dreamboat !!! ...and a &quot;HAPPY NEW YEAR&quot; to you as well. ...Dale

Being the end of the year, I feel it appropriate to THANK YOU on behalf of __ALL__ &quot;TEK-TIPPERS&quot; for devoting SO MUCH of your life to &quot;those in need&quot; here at Tek-Tips.

So... T H A N K S V E R Y M U C H ! ! !

Affectionately Signed...
Tek-Tipper #1, Tek-Tipper #2, Tek-Tipper #3, etc, etc.

 

[Dreamboat]

LOL Dufus! And the same to you, for sure!

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[Hasit]

No idea what number Tek-Tipper I am, but ditto on Dale's comments, although I have to say Thanks to Dale too, for saving my skin on a really tough problem.

Happy New Year all! :-D