Delete empty space at end of row

[huntforcare]

Hi, I have a sheet with 1002 rows of formulas pulling data in from other sheets, i then run a macro to sort the data (to get rid of blank rows and sort in number order), once this macro is run to sort the data im left with 103 rows of data (in this case).But the code posted is returing cell 1002 and not 103? Help me.

[mdmackillop]

I think we need to see all of your code. If you could post a sample workbook, that would be even better.

[Ryan Remole]

Here is one way to kill the dead space at the end of the sheet. I'm sure there are others. I clean up certain sheets upon closing to keep them smaller and running efficiently. See attached.



[vba]
Sub Ohappyday()
'yes, it deletes empty formatted rows also!!!
'but I typically don't have formatted cells after a table or database...
var1 = ActiveSheet.UsedRange.Rows.Count
MsgBox var1
If var1 = 1 Then Exit Sub
For var2 = var1 To 0 Step -1
If WorksheetFunction.CountA(ActiveSheet.Rows(var2 & ":" & var2)) > 0 Then Exit For
Next
If var1 > var2 Then ActiveSheet.Rows(var2 + 1 & ":" & var1).Delete Shift:=xlUp
End Sub

[/vba]




[VBA]
MsgBox ActiveSheet.UsedRange.Rows.Count
MsgBox ActiveSheet.UsedRange.Columns.Count

[/VBA]

[YasserKhalil]

well Mr. Ryan Remole
your code for deleting the space for rows .. What about columns?

[Ryan Remole]

well Mr. Ryan Remole
your code for deleting the space for rows .. What about columns?

Yes, the columns were much more fun!

The "UsedRange" method may be problematic for those with blank rows or columns before the sheet contents. Here is an example without it.

This method still deletes all at once as opposed to line by line.

[VBA]
var1 = ActiveSheet.Columns.Count - 1
If var1 = 1 Then Exit Sub
For var2 = var1 To 0 Step -1
If WorksheetFunction.CountA(ActiveSheet.Columns("A:A").Offset(0, var2)) > 0 Then Exit For
Next
Dim var3 As String, var4 As String, var5 As String
var3 = ActiveSheet.Range("A1").Offset(0, var2 + 1).Address
var4 = ActiveSheet.Range("A1").Offset(0, var1).Address
ActiveSheet.Columns(Left(var3, Len(var3) - 2) & ":" & Left(var4, Len(var4) - 2)).Delete

[/VBA]

[YasserKhalil]

Mr. Ryan Remole
The code doesn't delete the columns as I expected
Is there anything wrong about the code?

[mdmackillop]

Using Find
[VBA]Option Explicit
Sub DelColumns()
Dim LRw As Long, LCol As Long
LRw = Cells.Find("*", Cells(1, 1), , , xlRows, xlPrevious).Row + 1
LCol = Cells.Find("*", Cells(1, 1), , , xlColumns, xlPrevious).Column + 1
Rows(LRw & ":" & Rows.Count).Delete
Columns(LCol).Resize(, Columns.Count - LCol).Delete
End Sub
[/VBA]