Coding after the Last Row

  • I’m have a worksheet that will be populated by reports. Each report is likely to contain a different number of rows. I’m finding the last row of the main data range by using the far-right column (X). This will work perfectly for these reports.

    My problem is that the next 3 lines beneath the last row of the main body of data contains some information such who ran the report, the date, etc. These are on the left side of the page in the first several columns.

    I want to merge and aligned these rows differently and use a smaller and lighter font than the rest of the page.

    What is the best way to locate these next lines? I’ve tried using similar coding as I did for the last row, only I used column A. This seemed to get me close, but I could include the next couple of rows.


    Thanks, any help is appreciated.

  • Hello and Welcome to the Forum :)


    From your description, you seem to create a Footer for a print ...


    Is this correct or not ?


    See link to create Footer : https://www.dummies.com/softwa…-or-footer-in-excel-2013/


    You say you want to merge cells and rows ... :evil:

    You should do everything to avoid merging cells and rows ... !!!


    As far as locating these rows ... you say you already have the variable which returns the last row ...

    So you can easily have : lastrow + 3 ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Carim, Thanks for the quick response and the link, but that isn't exactly what I was looking for.

    Fine ... No problem ...


    What is exactly the question ... ?


    Is lastrow + 3 ... OK or not ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • I’m trying something like the following. The Lastrow part is working. But I keep having problems with the InfoRow lines.



    Dim Lastrow As Long


    Lastrow = ActiveSheet.Cells(Rows.Count, "X").End(xlUp).Row


    With ActiveSheet.Range("A4:X" & Lastrow)


    .EntireRow.RowHeight = 18

    .HorizontalAlignment = xlCenter

    .Font.Name = "Arial Narrow"

    .Font.Color = Auto

    .Font.Size = 11

    .Font.Bold = False

    .Borders.LineStyle = xlContinuous

    End With




    Dim InfoRow As Long


    InfoRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1


    With ActiveSheet.Range( --- I keep having trouble here --- )


    .EntireRow.RowHeight = 16

    .HorizontalAlignment = xlCenter

    .Font.Name = "Arial Narrow"

    .Font.Color = Auto

    .Font.Size = 8

    .Font.Bold = False

    .Borders.LineStyle = xlContinuous

    End With

  • Hi again,


    Cannot guess what is in front of your eyes ...;)


    Could you give an example of your three last rows ...?


    May be the following could help


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Carim,


    Thanks that got me far enough to do a little tweaking and it works just like I was expecting.


    If you don't mind I like to ask you a couple of other quick questions.

    I want to insert a line at the top of my worksheet to be used for the page title. I tried using


    Worksheets.Rows(Row1).Insert


    and I get an error.


    Also, Since these reports differ in the number of rows, I need to begin by clearing the colors and borders for the entire worksheet.


    How would I write that ?


    Thank again -

  • Glad you could fix your problem ...


    In order to Insert a Row in row # 1


    Code
    Rows("1:1").Insert Shift:=xlDown


    Regarding clearing the colors and borders ... are you dealing with Font colors or Interior colors ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • For Borders


    Code
    Cells.Borders.LineStyle = xlNone


    For Interior colors


    Code
    Cells.Interior.Color = xlNone

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thanks for the Like

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello,


    Could you kindly mark your thread as Solved ...


    Thanks

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!