sawilliams
Technical User
I get data output from an online application. Basically it’s name and address. Since the data is being entered by the general public, there are no standards as far as using Proper Case or Title Case in any of the fields especially the Address Field. I get the data in Excel and I fix it using these steps:
[ul]
[li]Insert column to right of Address1 column (say, create a blank Column G)[/li]
[li]In G1, I enter “=Proper(F1)”[/li]
[li]I copy that down by hovering my cursor over the lower right corner till it is a “+” and double-click to autofill[/li]
[li]Then I select column G and copy, paste special, values[/li]
[li]Then I delete column F[/li]
[/ul]
But, when I create a macro to do this, it sets the autofill range to be the number of rows with data in the present spreadsheet. The example below has 265 rows of data. However, the next output from my online app might have 400 rows. For that spreadsheet, my macro runs properly but only applies proper case to the first 265 rows and when it does the copy and paste, the result is that all cells in column G below row 265 are blank which makes sense because my “proper” code was not applied to those cells. So, is there some code I can put in place of (“G1:G265”):
Selection.AutoFill Destination:=Range("G1:G265")
that tells the macro to go as far down as there are cells with data? This is just a small segment of my macro. It performs all kinds of updates and corrections on multiple columns and I want to make it as fool-proof as possible for my end-user. Any help you can offer would be greatly appreciated.
Sub Macro1()
'
' Macro1 Macro
'
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Range("G1").Select
ActiveCell.FormulaR1C1 = "=PROPER(RC[-1])"
Range("G1").Select
Selection.AutoFill Destination:=Range("G1:G265")
Range("G1:G265").Select
Columns("G:G").Select
Selection.Copy
Columns("G:G").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
End Sub
[ul]
[li]Insert column to right of Address1 column (say, create a blank Column G)[/li]
[li]In G1, I enter “=Proper(F1)”[/li]
[li]I copy that down by hovering my cursor over the lower right corner till it is a “+” and double-click to autofill[/li]
[li]Then I select column G and copy, paste special, values[/li]
[li]Then I delete column F[/li]
[/ul]
But, when I create a macro to do this, it sets the autofill range to be the number of rows with data in the present spreadsheet. The example below has 265 rows of data. However, the next output from my online app might have 400 rows. For that spreadsheet, my macro runs properly but only applies proper case to the first 265 rows and when it does the copy and paste, the result is that all cells in column G below row 265 are blank which makes sense because my “proper” code was not applied to those cells. So, is there some code I can put in place of (“G1:G265”):
Selection.AutoFill Destination:=Range("G1:G265")
that tells the macro to go as far down as there are cells with data? This is just a small segment of my macro. It performs all kinds of updates and corrections on multiple columns and I want to make it as fool-proof as possible for my end-user. Any help you can offer would be greatly appreciated.
Sub Macro1()
'
' Macro1 Macro
'
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Range("G1").Select
ActiveCell.FormulaR1C1 = "=PROPER(RC[-1])"
Range("G1").Select
Selection.AutoFill Destination:=Range("G1:G265")
Range("G1:G265").Select
Columns("G:G").Select
Selection.Copy
Columns("G:G").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
End Sub