I run an Excel VBA Macro that generates a monthly report. This macro would read a column data and copy to another sheet for analysis purposes. This post shows how to use VBA macro to select a dynamic range of data.
I have tried Macro recording to generate the VBA code; it is not effective as the number of records changes every month. The recorded Macro is not robust enough to select all the records. It only selects the range that I have selected earlier. After some search on the internet and vibe coding, here is my solution.
Example 1
I need to copy all the data from "B4" to "N90" in "sheet1". The code below will select from B4 and to the right until end of data range, then select downwards until end of data range.
The code is dynamic enough that it can select data if is "B4" to "Z200" in "sheet1".
Sheets("sheet1").Range("B4",Sheets("sheet1").Range("B4").End(xlToRight).End(xlDown)).Select
Example 2
I need to copy all the data from "B4" and downwards in "sheet1". The code below will select from B4 and select downwards until end of data range.
The code is dynamic enough that it can select the range data regardless on the number of rows in "B" column.
Sheets("sheet1").Range("B4",Sheets("sheet1").Range("B4").End(xlDown)).Select