Managing Range Selection in Excel Macro
I have an Excel workbook with a bunch of tabs that refresh from a connected Access database. The refreshed data fills columns starting with A and then out to the right for between 5 and 12 columns. For a special purpose, I have formulas further out to the right which perform conversions on some of the refreshed data and I want to have a Macro that does a couple of things:
1: Deletes all of the formula rows below the range of the refreshed data. (So, if the refreshed data fills up down to row 10, for example, I want to delete all rows from 11 on down.)
2: "Copy/Paste/Values" of the formula data in on top of the refreshed columns. (So the data calculated by the formulas replaces the data brought in from Access.)
I tried to record a macro to do this, and most of it works OK. My problem is that to identify the first "blank" row in the refreshed data, I have been moving the Excel cursor to the top of one of the refresh columns (one I KNOW has data for all rows) and then using the "End/Down" to get to the bottom populated row. Then, to get to the NEXT row down, I hit the down arrow key, and this is the problem. The Macro does not record "Down One Row." It records the explicit selection of the cell I moved into - e.g. "H11". This means the macro will only work for this specific import and if a new refresh pulls in a different number of rows, the Macro is useless.
How can I replace the explicit selection of the cell with something generic that identifies the "Next Row Down" so that my delete command will remove the desired rows regardless of how the number of rows varies from refresh to refresh?
Thanks.
1: Deletes all of the formula rows below the range of the refreshed data. (So, if the refreshed data fills up down to row 10, for example, I want to delete all rows from 11 on down.)
2: "Copy/Paste/Values" of the formula data in on top of the refreshed columns. (So the data calculated by the formulas replaces the data brought in from Access.)
I tried to record a macro to do this, and most of it works OK. My problem is that to identify the first "blank" row in the refreshed data, I have been moving the Excel cursor to the top of one of the refresh columns (one I KNOW has data for all rows) and then using the "End/Down" to get to the bottom populated row. Then, to get to the NEXT row down, I hit the down arrow key, and this is the problem. The Macro does not record "Down One Row." It records the explicit selection of the cell I moved into - e.g. "H11". This means the macro will only work for this specific import and if a new refresh pulls in a different number of rows, the Macro is useless.
How can I replace the explicit selection of the cell with something generic that identifies the "Next Row Down" so that my delete command will remove the desired rows regardless of how the number of rows varies from refresh to refresh?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There are also ways and means of not physically selecting the cell to ascertain the extent of the data but I don't recall the syntax using the "Rows.Count" property. For the first blank row it would be "Rows.Count +1". This also gets round the need for the data in the column to be continuous, ie scenarios with non-continuous data where the End/Down wouldn't work.
Thanks
Rob
Thanks
Rob
What I think you are looking for is;
if your data column is H then
range("H1").end(xldown).of
will give the first blank row below the data if H1 and H2 are not blank.
if your data column is H then
range("H1").end(xldown).of
will give the first blank row below the data if H1 and H2 are not blank.
I assume therefore that:
Range("H1048576").end(xlup
Would give same result but allow for scenario where column H may not be continuous.
Range("H1048576").end(xlup
Would give same result but allow for scenario where column H may not be continuous.
ASKER
All your comments were outstanding and very helpful.
Thanks
Rob H