Excel - Macro to move row to last row

Asked By Jennife on 05-Sep-07 02:02 PM
I have a handful of montly invoices, where the line items increase / decrease
the amount of rows I may have from month to month.  The client wants us to
have Invoice Number and the Date at the bottom of the page.

I have it setup that G48 has Invoice Number and H48 has a date formula,
justified so it shows "Invoice # - Date"  We want our logo image below that,
so I have that setup as a footer.  Excel is finicky with image footers, so
having the Invoice # & Date in the footer does not play nice with the logo.
I'm just trying to think of a macro that I could run either manually or on
save / exit that would move those 2 cells to the last row of the worksheet.
We use A4 paper.  I hope this enough information & thanks for the assistance.

SixSigmaGu replied on 05-Sep-07 02:20 PM
This might help.  It will tell you what the last row is, then you can simply
copy the data from the current place to the new place:

Function iLastRowFilledInColumn(ws As Excel.Worksheet, iColumn As Long) As
Dim iLastRow As Long

iLastRow = ws.Cells(ws.Rows.Count, iColumn).End(xlUp).Row
' If the entire column is empty, Excel still returns 1 even though
' there is no data in row 1.  Therefore, check to see if the
' cell is empty; if it is, return 0 instead of 1.
If iLastRow = 1& Then
If Trim(ws.Cells(iLastRow, iColumn)) = "" Then
iLastRow = 0&
End If
End If

iLastRowFilledInColumn = iLastRow
End Function
Jennife replied on 05-Sep-07 02:34 PM

Thank you for the response.  That is a good solution to a more complex
problem, but doesn't really save anymore time (maybe a little) than just
using the Page Break Preview and inserting or deleting the amount of rows
that places it at the end of the end of the worksheet.  I was just hoping to
take it one step further, if possible.  Mostly because I forget to go back to
look at it before print, then have to reprint (the little tree-hugger inside
hates that).