Macro to delete blank rows 2

[Chuck712]

I have a spreadsheet that gets its data from a .pdf document report. I have been able to reduce this to get the data I need into 2 separate columns, but I have numerous blank rows with no data that I need to eliminate before I proceed. Without going through and manually deleting all of those rows, has anyone used anything to copy the sheet to another sheet and eliminate those balnk rows?
Thanks

 

[TonyJollans]

Hi Chuck712,

I just recorded this macro. It selects your two columns, filters them to hide blank cells in column A and copies the remaining values to the first two columns of another sheet. I'm sure you can tweak it to fit your exact needs.

Sub Macro1()

'
' Macro1 Macro
' Macro recorded 13/05/2003 by Tony Jollans
'

'

    Columns("A:B").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:=&quot;<>&quot;, Operator:=xlAnd
    Selection.Copy
    Sheets(&quot;Sheet2&quot;).Select
    Range(&quot;A1&quot;).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End Sub

Enjoy,
Tony

 

[Chuck712]

Thanks. This works great. Still need to "tweak" that screwy input data, but this is a life saver

 

[KenWright]

As long as they were truly blank, this will delete all completely blank rows:-

Sub DeleteEmptyRows()
'John Walkenbach
'Will delete all rows that are entirely 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
Next r
End Sub

Regards
Ken...............

 

[Dreamboat]

You can use the macro, but here's the "easy", non-VBA way:

Select the column that will ALWAYS have data if there's a record in that row. It's usually, but not always, column A.

So, select the column.
Hit Edit-Go to-Special, choose Blanks, hit OK.
Hit Edit-Delete.
You'll be asked to move cells up/whatever. Choose Delete Entire Row.

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[TonyJollans]

Dreamboat,

There is still so much I don't know. I did realise after I posted that this is not the VBA forum and that maybe code wasn't wanted but by the time I came back Chuck seemed happy so I left it, but your solution is better than mine.

Have a star for helping me learn.

Thanks,
Tony

 

[Dreamboat]

Thanks, Tony!
I just like to make sure that someone who might find these answers in a search realizes that there's a non-VBA way.



Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[Chuck712]

Thanks to all of you. I used all three methods. And it helped a great deal. The non-VBA way was a little quicker.
Thank Again
Chuck

 

[spkplf]

Hey Dreamboat.

Thanks for the non VBA solution, that help alot!

Similar question, I am importing a report via Document Direct that includes blank rows and header info from each page of the mainframe report. Any ideas on how to modify this spreadsheet in the fewest steps? There is one column that contains 2 character values so i thought a macro might be possible.