Need some help using setting variables to determine which cells I want to select in my range

Question

The amount of cells in my data table changes every week so I'm using a count function to determine the number of cells with data then using that count as a variable to put into my range(cells(x,x),cells(x,x) function to select. But I'm having an issue with taking the count and converting it to a variable to use. This is a basic macro I'm putting together for something else i'm doing.

Sub format_table()

Dim x As Long
Dim y As Long

''count the number of rows in rawdata table
Dim LastRow As Integer
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
x = LasRow



''count the number of columns in rawdata table
Dim LastCol As Integer
With ActiveSheet
  LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

y = LastCol


'''use the counted cells to determine a range to select
ActiveSheet.Range(Cells(1, 1), Cells(x, y)).Select


End Sub

Answer 1

I think you are having trouble trying to get the column number as the correct letter, right? Try something like this:

Sub Test()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    Dim ws As Worksheet
    Set ws = wb.Sheets("Sheet1") '(replace with whatever sheet name is)

    Dim lastRow as Integer, lastCol as Integer, lastColLet as Integer
    'get the number value of the last row and column
    lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lastCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    'uncomment the debug.print statements to see what it's getting for last row/column
    'Debug.Print lastRow
    'Debug.Print lastCol
    'get the letter that the column number corresponds to
    lastColLet = Letter(lastCol)
    'Debug.Print lastColLet

    ws.Range("A1:" & lastColLet & lastRow).Select
End Sub   
Function Letter(ByVal lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Letter = vArr(0)
End Function

It uses the function found here: Function to convert column number to letter? in order to convert the column # to a letter, then you can concat the letter and last row to select the way you wanted.

Answer 2

I guess your real issue is to decide what cells you actually need to select

your approach assumes that the left-upmost data cell is always in cell(1,1) and the down-right one is in the intersection of:

• last non empty row in column 1, last non empty column in row 1

should that be the case, then you can go on with your code provided you change x = LasRow to x = LastRow...

should not that be the case then you could assume that the range is the one delimited by:

• first non empty row in column 1, first non empty column in row 1

• last non empty row in column 1, last non empty column in row 1

then you could use this code:

Function GetData() As Range
    Dim firstRow As Long, firstColumn As Long
    Dim LastRow As Integer, lastColumn As Long

    With ActiveSheet
        firstRow = .UsedRange.Rows(1).Row '<--| get first used row index
        firstColumn = .UsedRange.Columns(1).Column '<--| get first used column index

        LastRow = .Cells(.Rows.Count, firstColumn).End(xlUp).Row '<--| get the first used column last non empty cell row index
        lastColumn = .Cells(firstRow, .Columns.Count).End(xlToLeft).Column '<--| get the first used rows last non empty cell column index

        'return the range
        Set GetData = .Range(Cells(firstRow, firstColumn), Cells(LastRow, lastColumn))
    End With
End Function

and exploit it in your main code as follows:

Sub format_table()


    With GetData '<-- use With-End With block to reference wanted object (Range) and avoid Select/Selection/Activate/ActiveXXX
         'format the referenced range
         .Font.Name=..  
         .Interior.Color=..
    End With


End Sub

but the GetData() function may still be not the one you need, should data be "jagged" in columns and or/rows with first row/column not hosting the last column/cell

so maybe you simply need:

Sub format_table()    

    With ActiveSheet.UsedRange
        'format the referenced range
'        .Font.Name=..
'        .Interior.Color=..
    End With    

End Sub