Depends what your data looks like. If you don't have data in row 7 then the current region method I shared will work. If you have data in row 7 you don't want included that becomes more involved. You could use the FIND in VBA to locate something unique in the bottom of your dataset and trap that row via a find and that way you never have to change your code provided you always have that unique item in the dataset.
You were almost there. You have to trap the value when you are working out what A will be equal to. Sub test() Dim A As Variant A = [C3].Value MsgBox A End Sub Give the above a try. Should work.
You can send a dynamic range between variables as follows Sub SendTO() Dim lr As Long lr = [A1048576].End(xlUp).Row Other lr End Sub Sub Other(TheLR) MsgBox TheLR End Sub This procedure sends the Last Row (LR) from SendTo to the Other procedure. This is how you get a variable to transfer between macros. Hope that helps.
Thank yo so much for the great help. I was able to successfully copy from one workbook to another. But unfortunately it didn't copy the dropdown part. Can you please suggest anything?
You can't set a range with the UsedRange method. You have to refere to the activesheet in the following way Activesheet.UsedRange. Which looks like you have recorded the macro. The above is more elegant in my opinion.
Perfecting dynamic ranges are a fundamental lesson in the VBA language. Practice this technique over and over to get good at it. :)
i only want the middle ? like row 6-10 and i want to be able to change it anytime without changing the codes in vba
Depends what your data looks like. If you don't have data in row 7 then the current region method I shared will work. If you have data in row 7 you don't want included that becomes more involved. You could use the FIND in VBA to locate something unique in the bottom of your dataset and trap that row via a find and that way you never have to change your code provided you always have that unique item in the dataset.
Hi. Thanks for this video.. but how can i make the address variable>>> something like:
let A as variant
A=C3
MsgBox [A].Value
You were almost there. You have to trap the value when you are working out what A will be equal to.
Sub test()
Dim A As Variant
A = [C3].Value
MsgBox A
End Sub
Give the above a try. Should work.
Thanks for the response.. But what I am asking is how to make the address of a cell as variable.
Not saving the value of a cell to a variable
@@not1AM I do not understand what you are asking. How do you know where the cell is unless you declare it?
How do you then use the Dynamic Range sub inside another macro so that VBA picks up the full data set when the amount of raw data is variable?
You can send a dynamic range between variables as follows
Sub SendTO()
Dim lr As Long
lr = [A1048576].End(xlUp).Row
Other lr
End Sub
Sub Other(TheLR)
MsgBox TheLR
End Sub
This procedure sends the Last Row (LR) from SendTo to the Other procedure. This is how you get a variable to transfer between macros. Hope that helps.
Thank yo so much for the great help. I was able to successfully copy from one workbook to another. But unfortunately it didn't copy the dropdown part. Can you please suggest anything?
This is very nice. Do you happen to know the difference between this and just using range.UsedRange?
You can't set a range with the UsedRange method. You have to refere to the activesheet in the following way Activesheet.UsedRange. Which looks like you have recorded the macro. The above is more elegant in my opinion.