Formatting columns of unknown length

[Guest]

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

 

[Guest]

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

 

[Ian]

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

 

[Guest]

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

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]

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
--

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?