After your sort, add code like:

Dim myRow As Integer
myRow = Range("I6").End(xlDown).Row
If myRow < 233 Then
Range(Range("I" & myRow + 1), Range("I233")).Resize(, 5).Delete Shift:=xlUp
End If

HTH,
Bernie
MS Excel MVP


"HGood" wrote in message
...
Hi, I've been studying the various posts related to this, but can't find any
that are close enough to this, to allow my very limited VBA skills to adapt
them. Thanks for any help you can offer.

I have a range I6:M233. I've constructed code to sort this by doing a macro,
so after sorting, all the data is at the top. Now I'd like help to add to
that sorting code so as to delete all the non data cells (though the empty
ones still contain formulas) below this data. Column I is the date, and if it
is empty, all 5 cells in that row can be deleted. But the entire row should
not be deleted because there is critical data to the left of column I.

Also, M234 is a total, so after the deletion code is finished, I'd like to
have this Total moved up to be under the bottom most row with data.

Thanks for any help you can give as to what the code should look like. I
know it needs to have Count in it but I don't know how to build code around
it.

Many thanks, Harold

Try this after your sort:

Dim myC As Range
Set myC = Range("I6:I233").Find(What:="", LookAt:=xlWhole)
If Not myC Is Nothing Then
Range(myC, Range("I233")).Resize(, 5).Delete Shift:=xlUp
End If

HTH,
Bernie
MS Excel MVP


"HGood" wrote in message
...
Thanks Bernie for this. I mis-stated earlier when I said there were formulas
below the data. There are not, but evidently there is something in those
cells that prevents it from working properly. Only if I select the "empty"
cells below the data and hit delete, will your code work properly, and then
it works great! The data got there in the first place in my macro by Copy,
Paste Special Values of data to the left of Col I.

Is there any way to tweak this code so it can ignore those cells with
something (possibly spaces) in them?

"Bernie Deitrick" wrote:

After your sort, add code like:

Dim myRow As Integer
myRow = Range("I6").End(xlDown).Row
If myRow < 233 Then
Range(Range("I" & myRow + 1), Range("I233")).Resize(, 5).Delete Shift:=xlUp
End If

HTH,
Bernie
MS Excel MVP


"HGood" wrote in message
...
Hi, I've been studying the various posts related to this, but can't find any
that are close enough to this, to allow my very limited VBA skills to adapt
them. Thanks for any help you can offer.

I have a range I6:M233. I've constructed code to sort this by doing a macro,
so after sorting, all the data is at the top. Now I'd like help to add to
that sorting code so as to delete all the non data cells (though the empty
ones still contain formulas) below this data. Column I is the date, and if it
is empty, all 5 cells in that row can be deleted. But the entire row should
not be deleted because there is critical data to the left of column I.

Also, M234 is a total, so after the deletion code is finished, I'd like to
have this Total moved up to be under the bottom most row with data.

Thanks for any help you can give as to what the code should look like. I
know it needs to have Count in it but I don't know how to build code around
it.

Many thanks, Harold