Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    VBA find last cell (2002 SP3)

    Hello,

    I am trying to create a macro which will enter specific formulas in specific columns. The number of rows will vary each the macro is used. I need some way to determine the last row so I can use the FillDown Method. Below is the result I get from a record macro.

    Sheets("Huidige maand").Select
    Range("G3").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range("G3:N1452").Select
    Selection.FillDown

    As you can see I have created the selection by Finding the last cell (cell R1457) and then shrink the selection by removing some columns form the selection. I was hoping that it would record the number of columns I moved to the right. But unfortunately it gave me a fixed range.

    Is there another way to create the selection for this. The selection should be G3: N*** where *** is the last row with data.

    I hope anyone can help. I have almost no VBA knowledge.

    Regards Marcel

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA find last cell (2002 SP3)

    Try this:
    <code>
    Dim n As Long
    With Sheets("Huidige maand")
    n = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    .Range("G3:N" & n).FillDown
    End With
    </code>
    Note that the range is not selected; this is generally more efficient than selecting a range.

  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA find last cell (2002 SP3)

    Hi Hans,

    Thanks for the quick answer.

    Can you give me a short explanation of what the 3rd line does?

    I forgot to mention that the cell where the formulas go are empty when the fill command is used. So I hope that this will work with the code you gave me.

    Regards Marcel

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA find last cell (2002 SP3)

    n = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Cells is the range consisting of the entire sheet.
    Find searches backwards (SearchDirection:=xlPrevious), row by row (SearchOrder:=xlByRows) until it finds any non-blank value (What:="*"). So it stops at the "lowest" non-blank cell in the sheet.
    Row returns the row number of this cell.

  5. #5
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA find last cell (2002 SP3)

    Hi Hans,

    I can understand this. It does seem easier then my code. But as I said I have little VBA knowledge. Thanks for the help.

    Regards Marcel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •