Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    HELP with VB and Excel (Excel 2000 VB 6)

    I have a situation that hopefully someone knows how to do this.
    What I need to do is dynamically create a sheet based on 4 recordsets, The first one is only 4 lines (Working) the second is working, what I need help with is the 3rd and 4th one.
    Here is what I need
    1. To find the last row that has information in it
    2. Skip 2 lines
    3. Write some text in Cells (like headings or something) then skip 2 rows
    4. insert the 3rd recordset
    repeate step 2, 3, 4

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

    Re: HELP with VB and Excel (Excel 2000 VB 6)

    Here is a function that finds the row number of the last used row in a worksheet:

    Function GetLastRow(ws As Worksheet) As Long
    GetLastRow = ws.Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row
    End Function

    And here is a procedure that uses this function to fill some cells below the last row:

    Sub FillSheet(SheetName As String)
    Dim rng As Range
    Dim ws As Worksheet
    Dim lngLastRow As Long

    ' Find last used row
    Set ws = ActiveWorkbook.Worksheets(SheetName)
    lngLastRow = GetLastRow(ws)

    ' Cell in column A, 3 rows below last used row (skip 2)
    Set rng = ws.Cells(lngLastRow + 3, 1)
    ' Set text
    rng.Value = "Hello World"

    ' Cell in column A, 3 rows below last one (skip another 2)
    Set rng = ws.Cells(lngLastRow + 6, 1)
    ' Do whatever you need to get the recordset
    End Sub

    Call it like this

    FillSheets "Sheet3"

  3. #3
    New Lounger
    Join Date
    Dec 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HELP with VB and Excel (Excel 2000 VB 6)

    Thanks Hans, I will give this a shot and see what happens, Looks good though I believe it might to the trick. Will let you know

Posting Permissions

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