Solved: Deleting Rows

[blumfeld0]

Hi. I have some data contained in N columns and there are spaces in between the rows. There is no pattern to the data and no frequency to the empty rows.
e.g
24 34 45 56
EMPTY ROW
56 98 21 9
21 48 31 17
EMPTY ROW
etc etc.
I just want to be able to delete only the empty rows and thus "move the row below it up"
anyone know of any macros or function in excel that will do this?
thanks.

[JKwan]

See if this will do for you:

Sub DeleteEmptyRows()
Dim LastRow, Index As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.UsedRange.Rows.Count
For Index = LastRow To 1 Step -1
If Application.CountA(Rows(Index)) = 0 Then Rows(Index).Delete
Next Index
Application.ScreenUpdating = True
End Sub

[blumfeld0]

hi! thank you. but it doesnt seem to work. im not sure why. does it work on your end?

any other ideas sir?
Im not sure if this matters-I have a worksheet and i have data calculated using formulas for other wokrhsheets.
this data has missing rows. i just want to be able to delete all empty rows in a highlighted region of this worksheet (or all empty rows in the wksht, doesnt matter)


thank you!

[JKwan]

yes, it works for me. How is it that it is not working. Did you put the codes into a module and then run it?

[blumfeld0]

hum.
Should I highlight the region on the worksheet that i want to run this macro on? or just leave it be?
does it make a difference that I have numbers as well as letters in the data?
does it matter that the numbers and letters in the data are actually calculated values using data in another worksheet?
thank you

[lucas]

Contributed by XLD. Deletes all rows that are empty in column A
Public Sub ProcessDataXLD()
Dim i As Long
Application.ScreenUpdating = False
With ActiveSheet

For i = 25 To 1 Step -1

If Application.CountBlank(.Cells(i, "A").Resize(, 20)) = 20 Then
.Rows(i).Delete
End If

Next i

End With
Application.ScreenUpdating = True
End Sub

[lucas]

Here is one that will delete blank rows only if the entire row is blank
Sub DeleteEmptyRows()
lastrow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = lastrow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 _
Then Rows(r).Delete
Next r
End Sub

[rory]

Can't you just select the data and sort it? then the empty rows will all be together.

[blumfeld0]

Hello. thank you! All your suggestions work but not for my particular file
beause I have failed to communicate the problem correctly. sorry about this.
let me try again-maybe this makes a difference.
yes my cells are blank but they are not empty because they contain formulas.
Namely, I use an IF statement like IF(A1<>A2,"",A2) then IF (B1<>B2,"",B2) etc etc. and drag this down and then repeat for columns C, D etc.
I end up with many blank rows because of the "" symbol I use in the IF statement for several columns of data.


Any ideas?
Thank you everyone!


Edit: Rory-Your suggestion almost works. But it tends to displace the data to other parts of the spreadsheet and makes it more disorganized in cases. It's hard to explain without getting into a lot of detail but thats what it does.
But I think I can live with this solution. Thank you!
If anyone has any other ideas please let me know.

[Bob Phillips]

Instead of



If Application.WorksheetFunction.CountA(Rows(r)) = 0 _


try



If Activesheet.Evaluate("SUMPRODUCT(--(" & r & ":" & r & "<>""""))") _

[blumfeld0]

Thanks XLD. I tried that and it gives me a 'runtime error 13'. 'type mismatch'
When I click debug it highlights

If ActiveSheet.Evaluate("SUMPRODUCT(--(" & r & ":" & r & "<>""""))") _
Then Rows(r).Delete

in yellow

[Bob Phillips]

What is the value of r at that point?