VBA setting range using variables

[mizogy]

Hi All

I've run into a problem, clearly I am doing something wrong! I am trying to set a range using both columns and rows as variants.
Please see code below.
Any help, much appreciated.
Best, Mizogy


Dim FirstRow As Variant

Dim LastRow As Variant

Dim ColumnStart As Variant

Dim ColumnEnd As Variant


FirstRow = Range("A1").Value

LastRow = Range("B1").Value

ColumnStart = Range(“A2”).value

ColumnEnd = Range(“B2”).value


Range(ColumnStart & FirstRow + 1 & ":" & ColumnEnd & LastRow).ClearContents

 

[MARK858]

What is actually in the cells the variables are referencing?

 

[jolivanes]

To make code readable and easier to copy, follow suggestion in picture.

 

[jolivanes]

Sub Maybe()
Dim FirstRow As Variant
Dim LastRow As Variant
Dim ColumnStart As Variant
Dim ColumnEnd As Variant
FirstRow = Range("A1").Value
LastRow = Range("B1").Value
ColumnStart = Range("A2").Value
ColumnEnd = Range("B2").Value
    Range(Cells(FirstRow + 1, ColumnStart), Cells(LastRow, ColumnEnd)).Select    '<----- Change to ClearContents when happy with the result
End Sub

BTW, the Dim's don't need to be variants. Single or Long works.

 

[MARK858]

Single or Long works.

Only if ColumnStart and ColumnEnd are numbers and not letters.

 

[jolivanes]

Yes, but that's what I assumed. Might be wrong, as you alluded to.
I guess we will, or maybe not, hear from the OP.

 

[jolivanes]

If your Column designations, Ranges A2 and B2, are alphabeticals, this should work.
Thanks to MARK858 for pointing this out.
Change the ".Select" to ".ClearContents" if satisfied that all works as needed.

Sub Maybe_2()
Dim FirstRow As Long
Dim LastRow As Long
Dim ColumnStart As String
Dim ColumnEnd As String
FirstRow = Range("A1").Value
LastRow = Range("B1").Value
ColumnStart = Range("A2").Value
ColumnEnd = Range("B2").Value
    Range(Cells(FirstRow + 1, Range([A2] & 1).Column), Cells(LastRow, Range([B2] & 1).Column)).Select
End Sub

 

[MARK858]

Range(Cells(FirstRow + 1, Range([A2] & 1).Column), Cells(LastRow, Range([B2] & 1).Column)

No need to make the changes in the line above, all you needed to change was the declarations as Cells accepts column letters
The code below would work fine

Sub Maybe()
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim ColumnStart As String
    Dim ColumnEnd As String

    FirstRow = Range("A1").Value
    LastRow = Range("B1").Value
    ColumnStart = Range("A2").Value
    ColumnEnd = Range("B2").Value
  
    Range(Cells(FirstRow + 1, ColumnStart), Cells(LastRow, ColumnEnd)).Select '<----- Change to ClearContents when happy with the result
End Sub

Test with the data below

Book1
	A	B	C
1	1	15
2	E	AC
3
Sheet2

 

[mizogy]

Sub Maybe() Dim FirstRow As Long Dim LastRow As Long Dim ColumnStart As String Dim ColumnEnd As String FirstRow = Range("A1").Value LastRow = Range("B1").Value ColumnStart = Range("A2").Value ColumnEnd = Range("B2").Value Range(Cells(FirstRow + 1, ColumnStart), Cells(LastRow, ColumnEnd)).Select '<----- Change to ClearContents when happy with the result End Sub

Thank you @MARK858 and @jolivanes for your help, much appreciated. Both suggestions worked based on either the column being alpha or numeric. Best, Mizogy.