Change last row of table to capitals

[Challis]

Hi
I have set up a code worksheet where users enter data into the last row of a table via entry through a userform with comboxes. Users fill out the form, and press a command button that fills the last row of the table.
I cant find a simple way to force the combo boxes to paste values into the table in uppercase only.
So instead, I want a code makes the last line added uppercase
Where am i going wrong:

Dim btmrow As Integer
Dim BottomRow As Long
'find next available row to update data in the data worksheet
    btmrow = Cells(Rows.Count, "A").End(xlUp).Row
'make everything added capitals
BottomRow = Range("btmrow:btmrow").Row
Range(BottomRow).Value = UCase(Range(BottomRow).Value)

 

[wideboydixon]

I don't think you can run UCase on a whole range; only on a single value. You'll need to work on each cell individually or use an array. Here's a solution working on each cell:

Public Sub LastRowToUpperCase()

Dim lastRow As Long
Dim myRange As Range
Dim myCell As Range

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Range(Cells(lastRow, 1), Cells(lastRow, Cells(lastRow, Columns.Count).End(xlToLeft).Column))
For Each myCell In myRange
    myCell.Value = UCase(myCell.Value)
Next myCell

End Sub

WBD

 

[Rick Rothstein]

I don't think you can run UCase on a whole range; only on a single value. You'll need to work on each cell individually or use an array. Here's a solution working on each cell:

Public Sub LastRowToUpperCase()

Dim lastRow As Long
Dim myRange As Range
Dim myCell As Range

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Range(Cells(lastRow, 1), Cells(lastRow, Cells(lastRow, Columns.Count).End(xlToLeft).Column))
For Each myCell In myRange
    myCell.Value = UCase(myCell.Value)
Next myCell

End Sub

WBD

You can eliminate the loop by replacing it with this...

myRange = Evaluate("UPPER(" & myRange.Address & ")")

 

[Sektor]

Sub MakeUCase()


    Dim BottomRow As Long
    Dim rngBottom As Range
    Dim i As Long
    Dim lastCol As Long
    'find next available row to update data in the data worksheet
    BottomRow = Cells(Rows.Count, "A").End(xlUp).Row
    'Get last row
    Set rngBottom = Rows(BottomRow)
    'Calculate last column in last row becase if we don't do it, we would iterate over 16384 cells.
    'NB: The usage of "Cells" property is REQUIRED, otherwise you will get error.
    lastCol = rngBottom.Cells(rngBottom.Cells.Count).End(xlToLeft).Column
    'Iterate over each cell starting from first cell in row (column A) till last column (lastCol).
    'If processed data starts NOT from first cell, then you need to adjust variable "i" in For loop.
    For i = 1 To lastCol
        rngBottom.Cells(i) = UCase(rngBottom.Cells(i))
    Next
End Sub

 

[Sektor]

You can eliminate the loop by replacing it with this...
myRange = Evaluate("UPPER(" & myRange.Address & ")")

This doesn't work with FULL row.

 

[Rick Rothstein]

This doesn't work with FULL row.

Give this a try instead...

myRange = Evaluate(Replace("IF(@="""","""",UPPER(@))", "@", myRange.Address))

 

[Sektor]

@Rick
What's the catch?

In your code you assign result of Evaluate back to Range variable? Am I correct?

 

[Rick Rothstein]

@Rick
What's the catch?

In your code you assign result of Evaluate back to Range variable? Am I correct?

Yes, but what is return is what was in the cell but now upper case (which is what I understood the OP's request to be)... look at the last line of code the OP posted in Message #1 .

 

[Challis]

Hey guys
Thanks. Sektors loop equation worked well.
Thankyou