Macro to set end to create range

[Guest]

I have a worksheet into which a file is imported daily and can vary in the
number of rows it occupies. I then does other things for which I have
recorded a macro, which includes going to the end cell. I use this to create
a range that then does lookups and creates data on another sheet. If the data
imported creates more rows than the sheet I have recorded the macro on I
don't get the extra data copied to the other sheet. I need a macro that will
tell me the last row on the imported sheet to set the end cell for the range
for the rest of my macro. Hopefully this will then "float" and I will always
get all of my data examined and transferred.



Regards
Chris.

 

[matt]

I have a worksheet into which a file is imported daily and can vary in the
number of rows it occupies. I then does other things for which I have
recorded a macro, which includes going to the end cell. I use this to create
a range that then does lookups and creates data on another sheet. If the data
imported creates more rows than the sheet I have recorded the macro on I
don't get the extra data copied to the other sheet. I need a macro that will
tell me the last row on the imported sheet to set the end cell for the range
for the rest of my macro. Hopefully this will then "float" and I will always
get all of my data examined and transferred.

Regards
Chris.

Chris,

I personally like the CurrentRegion property. Search "currentregion"
in the VBE help and you'll find an explanation of this property. In
short, if your data is contiguous, you can add a .Rows.Count to the
end of CurrentRegion and get the last row of your data set.

Range(x).CurrentRegion.Rows.Count

Matt

 

[Guest]

First, your code should clear all the contents of the input sheet (where the
data are imported) before importing. If that's true, then the expression
Worksheets("InputSheet").Range("A1").End(xlDown) will give you the last cell,
assuming contiguous data in culumn A. If not contiguous, you can use
Worksheets("InputSheet").Range("A65536").End(xlUp). Add ".Row" to either of
these to indicate the row number.