Set End-of-Range for Autofill in Excel Macro 3

[sawilliams]

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

 

[Andrzejek]

You should ask VBA questions in forum707

But try this code for your macro:

Dim r As Integer

r = 1

Do While Range("F" & r).Value <> ""
    Range("F" & r).Value = StrConv(Range("F" & r).Value, vbProperCase)
    r = r + 1
Loop

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

I'd also do this...

Do While [b]Trim[/b](Range("F" & r)).Value <> ""

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[sawilliams]

Thanks, Andrzejek! Sorry I posted in the wrong place. I wasn't thinking of it in terms of VBA but more using some special keystoke(s) while recording the Macro to do what you cleverly did in the Do While . But your answer is great. I will be able to use this solution in a number of places. Thanks.

Thanks, too, SkipVought. I added the TRIM as you suggested but I got an error and it wouldn't run. When I removed the TRIM, it worked fine again. Not sure why that was happening.

 

[sawilliams]

Guys, I have a follow-up question. Can I modify the code you supplied to apply to ANY column I select? I will want to change many different columns to Proper Case.

 

[Andrzejek]

Simple, ask for a column letter

Dim r As Integer
Dim strCol As String
[blue]
strCol = InputBox("Provide the column's Letter", "Tell me", "A")
[/blue]
r = 1

Do While Trim(Range(strCol & r).Value) <> ""
    Range(strCol & r).Value = StrConv(Range(strCol & r).Value, vbProperCase)
    r = r + 1
Loop

Trim should NOT give you an error, it is a VBA function and it should work.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[sawilliams]

Andrzejek! I AM having fun. Your InputBox is great. I'm learning a lot. And I see why the TRIM wasn't working -- closing parenthesis in the wrong spot. Thanks again!

 

[SkipVought]

Sorry!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Why are YOU sorry, Skip?
sawilliams put "closing parenthesis in the wrong spot". Let's blame him (or her)!

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

What me worry.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[sawilliams]

Hello. I'm back again. And I'm a "him" (Steve). I have a subsequent question (you may have created a monster). I have entered a formula in the first cell of a column and I want to autofill that formula to the last row of data. But again, I want this to be a macro because I will reapply it to various columns in different spreadsheets. I tried to write vba similar to the great suggestions I received earlier by using InputBox for a column letter prompt. That's are far as I get successfully. I don't know how to pass the variable.

Dim strCol2 As String

strCol2 = InputBox("Enter Column Letter to Reformat the data", "Tell me", "A")

    Columns(strCol2).Select
    Selection.AutoFill Destination:=Columns(strCol2)

Yes, clearly I'm bumbling. Any tips would be greatly appreciated!

And, if I should re-submit this in the VBA forum let me know and I will.

 

[Gavona]

Maybe this would do the trick? Depends on the structure of your sheet.

Dim strCol2 As String
Dim rFormula As Range
Dim rDestination As Range

    strCol2 = InputBox("Enter Column Letter to Reformat the data", "Tell me", "A")
    Set rFormula = Range(strCol2 & "1")
    Set rDestination = Range(strCol2 & "2:" & strCol2 & rFormula.SpecialCells(xlLastCell).Row)
    rFormula.Copy Destination:=rDestination
    rDestination.Value = rDestination.Value 'convert all but first row to values

Gavin

 

[sawilliams]

Gavona! Great tip. Thanks for the help. I modified just the last line from:

rDestination.Value = rDestination.Value 'convert all but first row to values

to:

rDestination.NumberFormat = "@"

to change the format to "text" because what I am doing is fixing zip codes that are exported from an online DB and which drop the leading zero in zip codes like 08540. Here's my whole code:

Sub Zip_Code_Zero_Fixer()

Dim strCol As String
Dim rFormula As Range
Dim rDestination As Range

strCol = InputBox("Enter Column Letter to the Right of the Zip Code Column", "Tell me", "A")

    Columns(strCol).Select
     
    Selection.Insert Shift:=xlToRight
    Selection.End(xlUp).Select
    ActiveCell.FormulaR1C1 = "=IF(OR(INDIRECT(""RC[-2]"",0)=""NJ"", INDIRECT(""RC[-2]"",0)=""CT"", INDIRECT(""RC[-2]"",0)=""MA"",
                                     INDIRECT(""RC[-2]"",0)=""ME"", INDIRECT(""RC[-2]"",0)=""NH"", INDIRECT(""RC[-2]"",0)=""RI"",
                                     INDIRECT(""RC[-2]"",0)=""VT""),""0"" & INDIRECT(""RC[-1]"",0), INDIRECT(""RC[-1]"",0))"
    Set rFormula = Range(strCol & "1")
    Set rDestination = Range(strCol & "2:" & strCol & rFormula.SpecialCells(xlLastCell).Row)
    rFormula.Copy Destination:=rDestination
    rDestination.NumberFormat = "@"

End Sub

So, again, thanks. Works great! Really helpful!

 

[Andrzejek]

I have entered a formula in the first cell of a column and I want to autofill that formula to the last row of data."
You don't really need a macro to do this.

Let's say you have data in Excel like this:

[pre]
FieldA FieldB Sum
1 12 13
2 23
3 34
4 45
5 98
6 65
7 36
[/pre]
Cell C2 has a formula [tt]=SUM(A2:B2)[/tt] so A2 + B2 is 13

You can simply select cell B3, move the cursor to the lower-right corner of the selected cell until your cursor changes to a cross, and double-click. It's magic!

You can also go back to C2, change your formula to, let's say [tt]=SUM(A2:B2) [blue]* 2[/blue][/tt] and do the same: move the cursor to the lower-right corner of the selected cell until your cursor changes to a cross, and double-click. New magic happens

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

...AND...

If you make your table a Structured Table (which is really easy via Insert > Tables > Table) then when you enter your formula, as Andy suggested above, it will automatically propagate through all rows in the Structured Table.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[sawilliams]

Great tips. Thanks, guys. What I'm doing is correcting the output from a messy online contribution site. When entering their name and address, the donors enter all manner of bad abbreviations and iMpRoper cASE and I need my users to be able to rapidly update 1) mixed case to Proper or Title Case ("nEW yORK" to "New York", "JOHN" to "John", etc.), 2) addresses that don't adhere to Postal Standards (like "Street" instead of "St", "#14" instead of "Apt 14", etc.), 3) zip codes that export to Excel and lose the leading zero (like "08540" becoming "8540"). They have to do some of these updates to multiple columns and re-do the whole shebang every time new data is downloaded from the website. I'm using macros with buttons in the Quick Access Toolbar in Excel to allow them to rapidly update. Particularly when I'm using a formula such as :

=IF(OR(INDIRECT(""RC[-2]"",0)=""NJ"",
       INDIRECT(""RC[-2]"",0)=""CT"",
       INDIRECT(""RC[-2]"",0)=""MA"",
       INDIRECT(""RC[-2]"",0)=""ME"",
       INDIRECT(""RC[-2]"",0)=""NH"",
       INDIRECT(""RC[-2]"",0)=""RI"",
       INDIRECT(""RC[-2]"",0)=""VT""),""0"" & INDIRECT(""RC[-1]"",0), INDIRECT(""RC[-1]"",0))"

which I can't have them recreate each time they open a new version of the download. So, I thought Macro was the most expedient way to handle these repetitive tasks. But I am open to suggestions if you all think I'm taking the long way to get where I need to be.

 

[Andrzejek]

correcting the output from a messy online contribution site"
I would guess this 'output' is not an Excel file, more likely it is a CSV (text) file? Am I right?
Excel is a good tool to use to correct the date, but if you deal with some kind of text file to start with, it maybe be easier to fix/correct the text file.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[sawilliams]

Yep, it is a *.csv. And I get out whatever our customers enter in. There is no functionality to fix the output before it becomes a *.csv. I need to standardize addresses to compare to existing records in our CRM to de-dupe and update or add new customer records. Right now, it's all done via manual lookups and manual edits. Hopefully when I get my process in place, it will save lots of busywork, or at least pare it down. The great help I've gotten here will make that happen.

 

[SkipVought]

I get whatever our customers enter in"

GIGO!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Yes, GIGO – garbage in, garbage out.
"There is no functionality to fix the output before it becomes a *.csv." - that's fine. (Not really, but that's life...)
But if that would be my job to correct the data, I would deal with csv (text) file directly, fixing data in there, instead of fighting Excel's interpretation of the data in the CSV file.

Read a line of data from CSV file
Parse it (Split() by comma)
Fix pieces needed to be fixed
Write the line of data into new csv file, of table in DB, or whatever.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.