Help with Macro's

[TheGr8Wizard]

Hello,
I appreciate any help I can get with creating a macro.

Each month I musty reformat and work with data from different workbooks, I would like to create a macro to do some of the formatting changes however each month there is a different number of rows and columns. I would like to know if there is a way to have an excel macro select data in a spreadsheet when the number of row or columns is unknown. I should add that most of the time the data is contiguous but not always.

Thanks,
Tim

 

[HalfAce]

Hi and welcome to the board.
My first suggestion would be to record a macro of all your actions to do this manually. That will generate the code necessary for what you've just done, then we can go in and modify it to make it more dynamic, etc.

One of the changes we'll make is to remove the specific rows & columns referenced in the recorded code and make it determine the last used row and column each time the code gets run. For example, to determine those you can use something like this:

Dim LstRw As Long, LstCol As Long
LstRw = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LstCol = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

and then refer to LstRw and LstCol in the code.
But, for this to really be meaningful we'll need to see the code for the rest of what you're doing.

Hope it helps.

 

[VoG]

That is a pretty vague request but look at

ActuveSheet.UsedRange