Formatting Data upto LastRow used only

**sonny.thind** · 12-19-2011, 12:42 PM

This is probably an easy fix but I am stuck with this macro. the macro Formats the data for the Rows and Columns . The Columns count remin the same but the Row count varies .
Right now the macro is hardcoded to go down to "Row 369". How Do I get this macro to customise to the amount of Rows used only?
Also I need this to automatically "prompt" to save in excel 2003 version.Can this code be modified to include that?

I have attached the file with the code..

**pike** · 12-19-2011, 09:31 PM

sonny.thind
some thing like

Please Login or Register  to view this content.

**sonny.thind** · 12-20-2011, 11:34 AM

hi Pike
but I am missing the boat here..do I use this macro in conjuncture to my macro--cause I keep getting the 'invalid Procedure error'--maybe I am too sleepy not sure??

**pike** · 12-20-2011, 04:50 PM

hi sonny.third
the code would be something like.....

Please Login or Register  to view this content.

**sonny.thind** · 12-21-2011, 01:00 PM

Thanks Pike this works perfectly.

But I was wondering how to I enter the code for future other worksheets --like I have different worksheet with formulas(see attached) in the rows and columns but which can increase over time . I created the macro like this by recording but the macro only runs till the row recorded so what do I need to change in the code to make this compatible with any future additional rows.
I need to know what extra code is needed.

**pike** · 12-21-2011, 02:34 PM

hi sonny.third

This is the code that sets the dynamic area you are working with

Please Login or Register  to view this content.

The start ara is B2 and column J

Cells(Rows.Count, 2).End(xlUp).Row

finds the last row number in column b

os combinded the range becomes say

With Range("B2:J30")

**sonny.thind** · 12-21-2011, 03:21 PM

Hi ,
will this work on the spreadsheet I just attached (summary report23) -this ones got formulas with conditional formatting.
so how do I set up the dynamic range for those ?

**pike** · 12-21-2011, 03:39 PM

Please Login or Register  to view this content.

Cells(Rows.Count, 1)
the 1 indicates which column to count the last row 1= A

Please Login or Register  to view this content.

you have to play around and add your formating

**sonny.thind** · 12-21-2011, 04:49 PM

Thanks Pike,
but the data and Formatting is totally different for the sheet I attached. I will probably start a new thread for this.
but the story with this sheet is

This is a sheet of Markets that I export into excel every day. So I recorded a Macro using the Macro Recorder which formats this sheet(see Code Macro8 in the Sheet Summary Report ) columns are for Dates and Rows are for the markets that list available deliveries , Number of Deliveries and the Percentage(that’s where the formula is) .The Formula needs to be copied into the percentage cells of all the markets.
Since I Recorded this macro sometime back we had only certain amount of Markets (rows) and only for 16 days (columns)but now we are growing and need this for two months and maybe more in future— instead of recording the macro again with additional rows—I need to create a dynamic range so that even if we have 10000 new rows and columns —the formula and formatting will apply.
How Do I amend this particular code?

do I need to start another thread? cause this involves formatting and Formulas?