I don't understand what happens with my Range. function-CodePudding

I'm writing a code in VBA for an Excel project and there is something I don't understand.

My code works, it does what I want. But there is one line I don't understand what is happening exactly.

Here is a part of my code :

Dim LastRow6 As Long

Dim LastColumn As Long

LastColumn = Sheets("All").Range("B2:DB2" & Columns.Count).End(xlToLeft).Row
'LastColumn = Sheets("All").Range(Selection, Selection.End(xlToRight)).Row
'LastColumn = Sheets("All").Range("2:2" & Columns.Count).End(xlToLeft).Row

LastRow6 = Range("B" & Rows.Count).End(xlUp).Row

Range("B3:B" & LastRow6).Select

Selection.AutoFill Destination:=Sheets("All").Range(ActiveCell.Address & ":DB" & LastRow6), Type:=xlFillDefault 'Autofills the formulas in the column selected to the right untill column DB which is the last header of the table

Range("B3").Select

Here, LastRow6 is a way for me to find the last cell in column B that has content in it. I use it because I want to select the cells with content in the column and drag their content to the right.

I want column B to be dragged to the right until column DB. This is why I specify in my autofill function ":DB".

The line of code that I don't understand is the following :

Selection.AutoFill Destination:=Sheets("All").Range(ActiveCell.Address & ":DB" & LastRow6), Type:=xlFillDefault 'Autofills the formulas in the column selected to the right until column DB which is the last header of the table

Can someone explain me how "&" works?

I want to understand it because I'm trying to find a way to drag the formula to the right if the last column is another one than DB. If I have new headers in my table, I what the autofill formula to be able to adapt to the new number of columns.

Thank you very much for your help!

CodePudding user response：

If you don't want to hard-code the last column (DB), then determine it:

With Sheets("All")
   Dim LastColumn As Long
   LastColumn = .Cells(2, .Columns.Count).End(xlToLeft).Column
   
   Dim LastRow As Long
   LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
   
   .Range("B3:B" & LastRow).AutoFill _ 
       Destination:=.Range("B3", .Cells(LastRow, LastColumn)), _ 
       Type:=xlFillDefault
End With

CodePudding user response：

Copy Formulas

To copy formulas, you can use the Range.Formula property which is more efficient than the Range.AutoFill method.
If you need to use the latter, replace .Formula = .Columns(1).Formula with
```
.Columns(1).AutoFill Destination:=.Cells, Type:=xlFillDefault
```
in the following code.
It is assumed that there is nothing (related to the Worksheet.UsedRange property) in the entire columns to the right and in the entire rows below the range of interest.

Sub CopyFormulas()

    With ThisWorkbook.Worksheets("All").UsedRange
        With .Worksheet.Range("B3", .Cells(.Rows.Count, .Columns.Count))
            .Formula = .Columns(1).Formula
        End With
    End With

End Sub

CodePudding user response：

The line of code that I don't understand is the following :

Selection.AutoFill  Destination:=Sheets("All").Range(ActiveCell.Address & ":DB" & LastRow6), Type:=xlFillDefault 'Autofills the formulas in the column selected to the right until column DB which is the last header of the table

Can someone explain me how "&" works?

The & token in VBA has two meanings, depending on context, i.e. how it's used (or sometimes, accidentally misused).

Type Hint: Long

If it's immediately after an identifier, then it's a type hint if that's not what you intended then you might be looking at an accidentally missing space between the identifier (or literal expression) and the & operator:

MsgBox "Hello, "& Name& "!"
_{The & here is a type hint for a Long Integer, hence the syntax error.}

Actual legal uses would include these:

Dim Value& '<~ As Long
Value& = 42& '<~ Long literal
Debug.Print "Value is a " & TypeName(Value&)
_{One of these things is not like the others...}

They are there for historical/compatibility reasons, it's just how types were declared way back when, in the times before the advent of the As clause. Nowadays we typically prefer readability over terseness.

But that's not why we're here.

String Concatenation Operator

When it's used in an expression, the & symbol is an operator that yields a String value, so its left-hand and right-hand sides (LHS and RHS, respectively) operands get coerced into strings as the expression is evaluated:

Dim Name As String
Name = "World"
MsgBox "Hello, " & Name & "!"

In the above snippet, the Name variable is declared as a String and its value is set to the string literal "World". When the MsgBox function call is being prepared, the arguments get evaluated; the MsgBox function accepts a String first parameter named prompt, so that's what is being evaluated: a string value ("Hello, ") that we concatenate with the RHS operand, a variable that evaluates to "World". Now at this point we have the string value "Hello, World", but then that's the LHS operand of another concatenation, this time with the string value "!". The resulting string is the argument that MsgBox receives into its prompt parameter.

The expression ActiveCell.Address & ":DB" & LastRow6 might evaluate to "A1:DB10341", and that's the string argument that gets passed to a (implicitly late-bound) Worksheet.Range call off a worksheet named "All", (implicitly) from the ActiveWorkbook. The resulting object reference is passed as the Destination parameter of the Range.AutoFill method, as the named argument syntax says (Destination:=...).

Selection.AutoFill  Destination:=Sheets("All").Range(ActiveCell.Address & ":DB" & LastRow6), Type:=xlFillDefault

Selection is whatever is currently selected in Excel. It could be a chart, it could be a shape or a button, ...or it could be a Range. The type is Object, which means AutoFill (or any other member call) is late-bound and there's no IntelliSense when you type out or edit the argument list, either. Declare and use a Range variable instead - it's an object reference, so we use the Set keyword to assign:

Dim Sheet As Worksheet
Set Sheet = ActiveWorkbook.Worksheets("All")

Dim Destination As Range
Set Destination = Sheet.Range(ActiveCell.Address & ":DB" & LastRow6) 

Dim Target As Range
Set Target = ActiveSheet.Range("B3:B" & LastRow6) 'sure that's the right sheet?

Target.AutoFill Destination, Type:=xlTypeDefault 'you get parameter IntelliSense here

So that's it: the line is a member call to Range.AutoFill with its arguments. By pulling complex argument expressions into a local variable like this, readability is improved, and everything is early-bound and compile-time checked, so no typo can come throw a run-time error 438 anymore (even more so with Option Explicit at the top of every module)!