christiemhjohnson
asked on
Using a variable in a VB Excel macro
I am writing a VB macro in Microsoft Excel which automatically creates a pivot table from two tabs within the spreadsheet. This macro will be used multiple times, each time with a different amount of data in it, in turn, I need to make the macro dynamic so that it will read in more or less data each time. I have written a loop to find the last row in my spreadsheet and saved that number as a variable (r or f) I want to use that variable in range type lines of code, but don't think I am doing it correctly.
For example, currently, I have
RANGE("F2:F10896").select
However, the next time this is run there will be more than 10896 records, so I need that to be dynamic. I can't imagine that I can use the following code:
RANGE("F2:FT").select
This is extremely important, so I can assure you an A and max points.
For example, currently, I have
RANGE("F2:F10896").select
However, the next time this is run there will be more than 10896 records, so I need that to be dynamic. I can't imagine that I can use the following code:
RANGE("F2:FT").select
This is extremely important, so I can assure you an A and max points.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Rory,
Thanks! It worked for my select statement, but didn't for my autofill. I think I might have done something wrong because I got the following error:
AutoFill method of Range class failed
This is what my code looks like....
Selection.Autofill Destimation:=Range("F2:F" &f)
Thanks! It worked for my select statement, but didn't for my autofill. I think I might have done something wrong because I got the following error:
AutoFill method of Range class failed
This is what my code looks like....
Selection.Autofill Destimation:=Range("F2:F" &f)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also, notice that I did not select any cells in the above solution. Selecting cells slows down your code and is not necessary in most situations.
Kevin
Kevin
Kevin,
Range("F2").Resize(r).Sele ct
and
Range("F2:F" & r).Select
are not the same though!
Rory
Range("F2").Resize(r).Sele
and
Range("F2:F" & r).Select
are not the same though!
Rory
Range("F2").Resize(r).Sele
Kevin