Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format sheets w/varying ranges
I need to format 5 sheets the same way; number formats, borders, etc. The
number of rows to format on each sheet is different. I'd like to loop through the formatting code but specify the range to format on each sheet. Starting row on each sheet is 9, but I need to determine the last cell and select the range to format. I'm trying to avoid repeating code. If you can help I'd really appreciate it. TIA, and have a great holiday! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format sheets w/varying ranges
If you want to find the last cell in the used range, check out this
code. It assumes that A9 is the upper-left cell of your range. Dim myLastRow As Long Dim myLastColumn As Long Dim myLastCell As String Dim myRange As String myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row myLastColumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column myLastCell = Cells(myLastRow, myLastColumn).Address myRange = "A9:" & myLastCell "myRange" will be the block of text consisting of whatever range is on the sheet. You can use this variable with any of the properties of the Range object, such as: Cut, Copy, Sort, Clear, etc ex: Range(myRange).Cut HTH, JP On Dec 20, 2:32 pm, cottage6 wrote: I need to format 5 sheets the same way; number formats, borders, etc. The number of rows to format on each sheet is different. I'd like to loop through the formatting code but specify the range to format on each sheet. Starting row on each sheet is 9, but I need to determine the last cell and select the range to format. I'm trying to avoid repeating code. If you can help I'd really appreciate it. TIA, and have a great holiday! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format sheets w/varying ranges
Awesome, JP! Thanks for the help. I'll be using this code in many workbooks
I'm sure. "JP" wrote: If you want to find the last cell in the used range, check out this code. It assumes that A9 is the upper-left cell of your range. Dim myLastRow As Long Dim myLastColumn As Long Dim myLastCell As String Dim myRange As String myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row myLastColumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column myLastCell = Cells(myLastRow, myLastColumn).Address myRange = "A9:" & myLastCell "myRange" will be the block of text consisting of whatever range is on the sheet. You can use this variable with any of the properties of the Range object, such as: Cut, Copy, Sort, Clear, etc ex: Range(myRange).Cut HTH, JP On Dec 20, 2:32 pm, cottage6 wrote: I need to format 5 sheets the same way; number formats, borders, etc. The number of rows to format on each sheet is different. I'd like to loop through the formatting code but specify the range to format on each sheet. Starting row on each sheet is 9, but I need to determine the last cell and select the range to format. I'm trying to avoid repeating code. If you can help I'd really appreciate it. TIA, and have a great holiday! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a chart with varying data value ranges | Excel Discussion (Misc queries) | |||
sumproduct with varying column ranges | Excel Worksheet Functions | |||
Calculation for varying ranges | Excel Worksheet Functions | |||
Copy cells with varying ranges (VBA) | Excel Programming | |||
making a formula over varying ranges | Excel Programming |