Using variables in Range

[Yamezz]

I have written (cobbled together is probably more accurate) many macros over the last 20 years. I'm by no means proficient, and the simple things still get me confused. One of those is the syntax for defining ranges.
From my current point of view, there seems to be no consistency to it all:
When to use Range
When to use Cells
When to use ,
When to use :
When to use &
When to use column then row
When to use row then column
Where the " quotation marks go

An example: Something like Range("A1:M" & LastRow) works, as does Range("A1", "M" & LastRow), but Range("A1":LastCol&LastRow) does not; niether does Range("A1", LastCol & LastRow)

My immediate problem that lead to writing this post is that I need the correct syntax for addressing A1:LastCol and LastRow
I'm familiar with Range("A1", "D" & LastRow), but cannot work out how to get LastCol AND LastRow in there.

I have searched in vain to find a simple explanation but have come up empty. Can someone point me to something I can bookmark, so I don't have to ask such simple questions on this forum?

 

[Scott Huish]

Range expects a Range type reference like A1 or A1:A50 for example

LastCol and LastRow, how people typically use these return numbers, so if LastRow was 50 and LastCol was 1, a valid range is not A1:150, it is A1:A50, which is why those don't work.
Quotation marks are used to delimit strings so you are actually using the text A1 to indicate to Range that it is referring to that text, as opposed to a variable A1.

Dim A1 as String
A1="A1"
Range(A1).Select 'This refers to the value in the variable A1
Range("A1").Select 'This refers to the string value directly.

Cells is used when you want to refer to ranges numerically. Here you could use those variables and only refers to a single cell.
Cells(LastRow,LastColumn) would be a correct usage of this assuming those variables contain valid numbers for this purpose, like it couldn't be 0 for instance.

This is where , come in. This is Union operator. So if you had 2 cells A1 (1,1) and H3 (3,8), you would use Range to combine all this together.
Range(Cells(1,1),Cells(3,8).Select would select A1:H3. I would use this method if I were using numerical variables for Row and Column.

& is a concatenation operator.
I would never use it this way exactly, but for illustration purposes. This would concatenate "A" & 1 to give you A1
Range("A" & 1).Select

and if you used a variable for the row.

Range("A" & LastRow).Select

 

[Micron]

but Range("A1":LastCol&LastRow) does not

because your colon character is not within quotes - Range("A1:" & LastRow)

 

[Yamezz]

Thanks for your replies.

Testing Range("A1", Cells(LastRow,LastCol)).Select works.

I still don't understand why the order swaps around though: Range("ColumnRow",Cells(RowColumn))

And we swap from Range(Cells(1,1):Cells(3,8)).Select to Range(Cells(1,1),Cells(3,8)).Select

 

[Yamezz]

To further expand this topic, I'm now looking for a variable equivalent of Range("C:C") to use in AdvancedFilter.
For example,

FilterCol = InputBox("Enter column to filter for unique values", "FilterCol")
ThisWorkbook.range(FilterCol:FilterCol).AdvancedFilter xlFilterCopy, , wb.ActiveSheet.Range("A1:A1"), True

What is the correct syntax to use in place of FIlterCol:FilterCol?

 

[MARK858]

because your colon character is not within quotes - Range("A1:" & LastRow)

That wouldn't work either , you need the column letter for the end cell Range("A1:A" & LastRow)

What is the correct syntax to use in place of FIlterCol:FilterCol?

You could just use

Dim FilterCol As String
FilterCol = InputBox("Enter column to filter for unique values", "FilterCol")
ThisWorkbook.Worksheets("YourWorkSheetName").Columns(FilterCol).AdvancedFilter xlFilterCopy, , wb.ActiveSheet.Range("A1:A1"), True

or

Dim FilterCol As String
FilterCol = InputBox("Enter column to filter for unique values", "FilterCol")
ThisWorkbook.Worksheets("YourWorkSheetName").Range(FilterCol & ":" & FilterCol).AdvancedFilter xlFilterCopy, , wb.ActiveSheet.Range("A1:A1"), True

 

[Micron]

That wouldn't work either , you need the column letter for the end cell Range("A1:A" & LastRow)

Whoops! Sometimes when I back up to edit things I omit stuff. Thanks.

 

[Yamezz]

Thanks Mark. I've tried to use ThisWorkbook.ActiveSheet and this trips up both versions of the code. Is my syntax incorrect in the following test lines?

ThisWorkbook.ActiveSheet.Range(FilterCol & ":" & FilterCol).AdvancedFilter xlFilterCopy, , wb.ActiveSheet.Range("A1:A1"), True
ThisWorkbook.ActiveSheet.Columns(FilterCol).AdvancedFilter xlFilterCopy, , wb.ActiveSheet.Range("A1:A1"), True

The error is 1004 AdvancedFilter method of Range class failed

 

[Yamezz]

Never mind, I've worked out my error.

 

[MARK858]

If you are using ActiveSheet then don't use a reference to the workbook as the ActiveSheet is the sheet that is active, irrespective of any workbook.