Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting columns of unknown length
Hi,
I wish to format certain columns (say A, B, F) to uppercase, but I don't know how long the columns will become. Is there a 'catch all' paramer rather than stating the range? tia -- Traa Dy Liooar Jock |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting columns of unknown length
Hi Jock,
Try this as worksheet code Sub stance() lastrowA = Range("A65536").End(xlUp).Row lastrowB = Range("B65536").End(xlUp).Row lastrowF = Range("F65536").End(xlUp).Row Set Range1 = Range("A1:A" & lastrowA) Set Range2 = Range("B1:B" & lastrowB) Set Range3 = Range("F1:F" & lastrowF) Set range4 = Union(Range1, Range2, Range3) For Each c In range4 c.Value = UCase(c.Value) Next End Sub Mike "Jock" wrote: Hi, I wish to format certain columns (say A, B, F) to uppercase, but I don't know how long the columns will become. Is there a 'catch all' paramer rather than stating the range? tia -- Traa Dy Liooar Jock |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting columns of unknown length
Private Sub Test()
Dim lastrow As Long For rownum = 1 To Cells(Rows.Count, "A").End(xlUp).Row Cells(rownum, "A").Value = UCase(Cells(rownum, "A").Value) Next For rownum = 1 To Cells(Rows.Count, "B").End(xlUp).Row Cells(rownum, "A").Value = UCase(Cells(rownum, "B").Value) Next For rownum = 1 To Cells(Rows.Count, "F").End(xlUp).Row Cells(rownum, "A").Value = UCase(Cells(rownum, "F").Value) Next End Sub Repeat for other columnscolumns B & F -- Ian -- "Jock" wrote in message ... Hi, I wish to format certain columns (say A, B, F) to uppercase, but I don't know how long the columns will become. Is there a 'catch all' paramer rather than stating the range? tia -- Traa Dy Liooar Jock |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting columns of unknown length
Thanks guys.
I couldn't get either to work tho. When there's more than one sub in a worksheet, I would presume that it will be actioned in the order the code is set out in. Is there any recommended default order in which to set things out ar does it really not matter too much? -- Traa Dy Liooar Jock "Ian" wrote: Private Sub Test() Dim lastrow As Long For rownum = 1 To Cells(Rows.Count, "A").End(xlUp).Row Cells(rownum, "A").Value = UCase(Cells(rownum, "A").Value) Next For rownum = 1 To Cells(Rows.Count, "B").End(xlUp).Row Cells(rownum, "A").Value = UCase(Cells(rownum, "B").Value) Next For rownum = 1 To Cells(Rows.Count, "F").End(xlUp).Row Cells(rownum, "A").Value = UCase(Cells(rownum, "F").Value) Next End Sub Repeat for other columnscolumns B & F -- Ian -- "Jock" wrote in message ... Hi, I wish to format certain columns (say A, B, F) to uppercase, but I don't know how long the columns will become. Is there a 'catch all' paramer rather than stating the range? tia -- Traa Dy Liooar Jock |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting columns of unknown length
You need to run the macro. If you need it to run automatically, you need to
trigger it in some way. One option is to use Worksheet_SelectionChange(ByVal Target As Range) in place of Test(). This will run the macro every time you select a different cell. Not efficient as it means the macor runs repeatedly. Another, possibly better option is to use Worksheet_Activate() or Worksheet_Deactivate(). This will not run as often, but you won't see the effects of the macro until you have deactivated and reactivated the sheet (normally after closing and reopening). Other options are available, both a Worksheet and Workbook level (including Before save and Before close). The choice is yours as it depends when you want to see the data updated. -- Ian -- "Jock" wrote in message ... Thanks guys. I couldn't get either to work tho. When there's more than one sub in a worksheet, I would presume that it will be actioned in the order the code is set out in. Is there any recommended default order in which to set things out ar does it really not matter too much? -- Traa Dy Liooar Jock "Ian" wrote: Private Sub Test() Dim lastrow As Long For rownum = 1 To Cells(Rows.Count, "A").End(xlUp).Row Cells(rownum, "A").Value = UCase(Cells(rownum, "A").Value) Next For rownum = 1 To Cells(Rows.Count, "B").End(xlUp).Row Cells(rownum, "A").Value = UCase(Cells(rownum, "B").Value) Next For rownum = 1 To Cells(Rows.Count, "F").End(xlUp).Row Cells(rownum, "A").Value = UCase(Cells(rownum, "F").Value) Next End Sub Repeat for other columnscolumns B & F -- Ian -- "Jock" wrote in message ... Hi, I wish to format certain columns (say A, B, F) to uppercase, but I don't know how long the columns will become. Is there a 'catch all' paramer rather than stating the range? tia -- Traa Dy Liooar Jock |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding a string of unknown length in a string of unknown length, Help! | Excel Discussion (Misc queries) | |||
Merge to Columns with Unknown length | Excel Programming | |||
Formatting Data Columns of various length with Macros | Excel Programming | |||
Formatting Data Columns of various length with Macros | Excel Programming | |||
Sum unknown length column data in VBA | Excel Programming |