CODEWITHSUNDEEP
excelvba
Vaggelis
Vaggelis

Reputation: 57

VBA ActiveCell Dynamic Resize

Hi there i have create the following code to format the Grand Total Row in excel spreadsheet. My problem is that I want to select dynamic the cells from Grand Total and right because I don’t have always 15 columns. I make a try with ActiveCell but it didnt work. Can anyone help me to change this code to fit my need?

Range("A1").Select
FindRow1 = Range("A:A").FIND(What:="Grand Total", LookIn:=xlValues, LookAt:=xlWhole).Activate
ActiveCell.Resize(, 15).Select

'Range(ActiveCell, Cells(, ActiveCell.End(xlToRight).Column)).Select

Selection.Font.Bold = True

With Selection.Interior
    .Pattern = xlSolid
     .PatternColorIndex = xlAutomatic
     .ThemeColor = xlThemeColorAccent6
     .TintAndShade = 0.399975585192419
    .PatternTintAndShade = 0

End With

Selection.Font.Size = 12

[EDIT]: Here's a screenshot of my problem after trying suggested solutions:

enter image description here

Upvotes: 1

Views: 1299

Answers (2)

NadAlaba
NadAlaba

Reputation: 302

  1. You don't have to select the range. Just be sure of the address of the range you're using, and you're good.
  2. It'd be better if you specify the worksheet you're working with, so if you have multiple sheets in the workbook you'd still be working on the right one.
  3. Instead of activating the cell you found with Find, pass the row of that cell to a variable called myRow and use this variable in another function to define the range you need.
  4. Once you have defined the range you need, pass it to a variable like myRange, and use it instead of using Selection in the rest of your code.
  5. To make your range change its size dynamically (assuming you want your range to have one row and all the filled cells of that row), then you'll need to find the column of the last filled cell in your table, pass it to a variable lastCol and use it to define your range.
Sub formatRange()

    Dim ws As Worksheet
    Dim myRow As Long, lastCol As Integer, myRange As Range

    Set ws = ThisWorkbook.ActiveSheet  'Change this to the name of the sheet you're working with

    myRow = ws.Range("A:A").Find(What:="Grand Total", LookIn:=xlValues, LookAt:=xlWhole).Row 'The row that has "Grand Total"
    lastCol = ws.Cells(myRow, Columns.Count).End(xlToLeft).Column 'The column of the last filled cell in `myRow`

    Set myRange = ws.Range("A" & myRow).Resize(1, lastCol) 'The desired range has 1 row and (lastCol) columns

    myRange.Font.Bold = True

    With myRange.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With

    myRange.Font.Size = 12

End Sub

  • Consider this case:

If the column of the last cell in the row myRow is NOT the same as the last column in the whole table (see screenshot), you have 2 choices to define your lastCol: Table Screenshot 1&2

  1. You can define lastCol as the last column of the row myRow (screenshot 1), and in that case you keep the code above as it is.
  2. You can define it as the last column of the whole table (screenshot 2), and in that case you'd have to replace the lastCol line above with this: 
        'The column of the last filled cell in the whole table
    lastCol = ws.Cells.Find(What:="*", _
                After:=Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious).Column

P.S, If the column of the last cell is the same in all your rows, you can ignore this last paragraph.

Upvotes: 2

DisplayName
DisplayName

Reputation: 13386

Another example as to how to avoid Select/Selection/Activate/ActiveXXX pattern and how to use nested With...End With structure:

With Range("A1", Range("A:A").Find(What:="Grand Total", LookIn:=xlValues, LookAt:=xlWhole).End(xlToRight))

    With .Font
        .Bold = True
        .Size = 12
    End With 

    With .Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With

End With

Upvotes: 1

Related Questions