Using Autosum and recording a Macro

[Jaysilva]

hi,

Thanks for taking the time to read this. Basically im trying to create a Macro so that i dont waste time formatting statements produced by my in house system at work. The data that is produced by the in house system needs totals in them and borders where appropriate. So far i have created several Macros but the only problem i am having is when the amount of rowss of data varies. When i have more rows of data the borders either stop half way through the date and the autosums either miss bits out of leave hugh amounts of gaps. Any help with this is appreciated i am a Newbie to this.

 

[JLGWhiz]

hi,

Thanks for taking the time to read this. Basically im trying to create a Macro so that i dont waste time formatting statements produced by my in house system at work. The data that is produced by the in house system needs totals in them and borders where appropriate. So far i have created several Macros but the only problem i am having is when the amount of rowss of data varies. When i have more rows of data the borders either stop half way through the date and the autosums either miss bits out of leave hugh amounts of gaps. Any help with this is appreciated i am a Newbie to this.

You can set a dynamic range to a variable and use that variable in the code to work with the range. There are several ways to do that, but they are all based on defining the last row of data for the range you want to work with. The simple solution is to assume you want your last row for the range to be the last row with anything at all in it, including a formula in a cell that might appear blank. To get that row number:

Dim lastRow As Long
lastRow = ActiveSheet.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row

The above snippet uses the VBA Find function to look for any string value greater than blank, including formulas, beginning at the default top left cell of the used range and working backward from bottom to top, right to left. It will initialize the lastRow variable with an iteger representing the row number where the first value greater than blank is found. You can then use the variable to create your dynamic range:

Sub t()  
 Dim lastRow As Long
lastRow = ActiveSheet.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row      
MsgBox ActiveSheet.Range("A2:D" & lastRow").Address
End Sub

You can try the short procedure on a sheet of data to see what the message box returns. But, be careful, becuase if your code adds or deletes row after you have initialized the variable, the variable may no long reflect the true last row number and could require resetting, depending on how you use it in the code. Yet, it will not require adjustment if rows are manually added or deleted between runs of the procedure.

 

[JLGWhiz]

Please do not attempt to resolve your issues by private email. Any exchange of information or ideas related to this thread should be done by posting to this thread so that all participants have an opportunity to review, and if applicable, to comment.