Reputation: 584
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
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
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