CODEWITHSUNDEEP
sqlvbaexceldynamic
Chase
Chase

Reputation: 584

Select Dynamic Table Range In Excel Using VBA

I am trying to select a dynamic table - I can figure out the copy/pasting part of the script, but I can't figure out how to initially select this table.

The table is dynamic in terms of both number of rows and number of columns. This is so because this lone workbook will need to be usable by different business units, calling different SQL server tables. So the user puts their inputs in Sheet1, refreshes the connection, and a table is returned in Sheet2.

Here are snippets, including a working and a broken portion:

    'Variable designations
    Dim rowcount As String
    Dim columncount As String
    Dim sheetref1 As String
    Dim sheetref2 As String
    Dim rangeselect1 As String
    Dim rangeselect2 As String

    rowcount = Cells(Rows.Count, 1).End(xlUp).Row
    columncount = Cells(1, Columns.Count).End(xlToLeft).Column
    sheetref1 = "Sheet1"
    sheetref2 = "Sheet2"
    rangeselect1 = "A2:A" & rowcount
    rangeselect2 = "A1:" & columncount & rowcount '<--BROKEN

    'Copy column with populated rows
    Sheets(sheetref1).Range(rangeselect1).Copy '<--WORKING

    'Copy table with populated rows and columns
    Sheets(sheetref2).Range(rangeselect2).Copy '<--BROKEN

So here rangeselect2 = "A1:" & columncount & rowcount I am trying to return something like A1:Z10 or A1:F3000 - the dynamic range (by the way, A1 is static). My attempt is for columncount to return "Z" or "F" or whatever the last column letter is, while rowcount (hopefully) properly returns the last row number.

Hopefully this makes sense. I will gladly answer any further questions, and I greatly appreciate any advice/help.

Upvotes: 0

Views: 5878

Answers (2)

Etheur
Etheur

Reputation: 347

Your rangeselect2 = "A1:" & columncount & rowcount isn't working because columncount and rowcount are coming back as numbers indicating the row and column. If you want to translate the columncount and rowcount into an A1 address (e.g. column 2 and row 5 is B5), then you can use Cells(rowcount, columncount).Address, which returns the absolute reference for that coordinate.

rangeselect2 = "A1:" & Cells(rowcount, columncount).Address should work for what you're trying to do here.

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19857

This will find and select all cells from A1 to the last cell containing data on Sheet1 of the workbook that the code is in:

Sub SelectDynamicTable()

    Dim rFinalRange As Range
    Dim lLastRow As Long
    Dim lLastCol As Long

    With ThisWorkbook.Worksheets("Sheet1")
        lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
        lLastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row

        If lLastCol = 0 Then lLastCol = 1
        If lLastRow = 0 Then lLastRow = 1

        Set rFinalRange = .Range(.Cells(1, 1), .Cells(lLastRow, lLastCol))
    End With

    rFinalRange.Select

End Sub

Use Cells rather than range - Cells can accept the row and column numbers rather than alpha designation.

This function will return a reference to the last cell when given the worksheet reference and the optional column to look at.

Public Function LastCell(wrkSht As Worksheet, Optional Col As Long = 0) As Range

    Dim lLastCol As Long, lLastRow As Long

    On Error Resume Next

    With wrkSht
        If Col = 0 Then
            lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
            lLastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
        Else
            lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
            lLastRow = .Columns(Col).Find("*", , , , xlByColumns, xlPrevious).Row
        End If

        If lLastCol = 0 Then lLastCol = 1
        If lLastRow = 0 Then lLastRow = 1

        Set LastCell = wrkSht.Cells(lLastRow, lLastCol)
    End With
    On Error GoTo 0

End Function

This test procedure will display the address of the last cell in column 2 of the activeworkbook:

Public Sub TestLastCell()

    MsgBox LastCell(ActiveWorkbook.Worksheets("Sheet2"), 2).Address

End Sub

If you want to find the first cell use xlNext rather than xlPrevious within the code.

Upvotes: 1

Related Questions