I want to select multiple cells which are not in a row or column but separate cells using the Range command.
The first line of code in the Sub is a recorded macro when selecting different separate cells.
I want to do this in a For-Loop sequence using variables.
To select cells using the loop and variables I concatenate the column letter and the variable. The column letter is in quotes. The Range command is also in quotes.
Sub test()
ActiveSheet.Range("D5, D1, F6").Select
End Sub
Sub multiple()
Dim weeknumber As Range
For TaskRow = 1 To 1
' Set weeknumber = Union(Range("C" & TaskRow), Range("E" & TaskRow)).Select
' Set weeknumber = Range(CHR(34)"C" & TaskRow, "E" & TaskRow CHR(34)").Select
weeknumber = Range("""C"" & TaskRow, ""E"" & TaskRow").Select
Next TaskRow
End Sub
A fix of your code could look like that
Sub multiple()
Dim weeknumber As Range
Dim TaskRow As Long
For TaskRow = 1 To 1
' Set weeknumber = Union(Range("C" & TaskRow), Range("E" & TaskRow)).Select
' Set weeknumber = Range(CHR(34)"C" & TaskRow, "E" & TaskRow CHR(34)").Select
' You have to use set when assigning an object variable
' you have to build a string like in your recorded code
Set weeknumber = Range("C" & TaskRow & ", E" & TaskRow)
Next TaskRow
' Select is usually not neccessary
' but for the sake of it
weeknumber.Select
End Sub
The Select method is often unnecessary because you can directly manipulate objects such as ranges, cells, and worksheets without selecting them first. This approach makes the code more efficient and easier to read.
Avoiding Select can make your code run faster. When you select an object, VBA has to switch the focus to that object, which takes additional processing time.