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

 

[SkipVought]

Ditto!

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

 

[zelgar]

It would probably be easier if the OP would open the document in Word, use macros there and save it as a CSV.

 

[sawilliams]

Hello. I'm back. I'm not sure why, but I'm not getting notifications that there are replies to this thread. I checked my settings and it appears that I should get emails but nothing is coming thru. So, I apologize for not commenting sooner on the last remarks. So, one of my Excel macro has hundreds of lines of code to fix all possible address anomalies. See just a snippet below:

    Selection.Replace What:=" Drive ", Replacement:=" Dr ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Drive", Replacement:=" Dr", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Dr. ", Replacement:=" Dr ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Dr.", Replacement:=" Dr", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Road ", Replacement:=" Rd ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Road", Replacement:=" Rd", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Rd. ", Replacement:=" Rd ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Rd.", Replacement:=" Rd", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Court ", Replacement:=" Ct ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Court", Replacement:=" Ct", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Ct. ", Replacement:=" Ct ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Ct.", Replacement:=" Ct", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Street ", Replacement:=" St ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Street", Replacement:=" St", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" St. ", Replacement:=" St ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" St.", Replacement:=" St", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Square ", Replacement:=" Sq ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Square", Replacement:=" Sq", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Avenue ", Replacement:=" Ave ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Avenue", Replacement:=" Ave", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Ave. ", Replacement:=" Ave ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Ave.", Replacement:=" Ave", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Place ", Replacement:=" Pl ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Place", Replacement:=" Pl", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Pl. ", Replacement:=" Pl ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Pl.", Replacement:=" Pl", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Turnpike ", Replacement:=" Tpk ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Turnpike", Replacement:=" Tpk", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Suite ", Replacement:=" Ste ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Ste. ", Replacement:=" Ste ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Lane ", Replacement:=" Ln ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Lane", Replacement:=" Ln", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Ln. ", Replacement:=" Ln ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" Ln.", Replacement:=" Ln", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

And that is just a small segment. We go strictly by Postal Standards for our addresses so this clean-up is necessary. I'm not sure how this could be done simpler in Word or any other application but I'm always open to suggestions. I also fix mixed case (all UPPER or all lower) to Proper or Title Case. And I a concatenate Address Line 1 and Address Line 2 into one column. And I fix zipcodes that have dropped leading zeros such as "8540" which should be "08540". The great tips I've gotten on this forum have really made this process easy to run.

But, of course, I do have a follow up question. I have added the macros to buttons on the quick access toolbar in Excel and they work great. But if I click on a button that prompts me to enter a column letter (see below) and I "cancel" I get a "run time error '13'" Type Mismatch. Any suggestions on what I can do to prevent that?

Sub Concatenate_Address_Lines()

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

strCol = InputBox("Enter Column Letter to the RIGHT of the 2nd Address Line Column", "Concatenate Address Lines", "A")

    Columns(strCol).Select

    Selection.Insert Shift:=xlToRight
    Selection.End(xlUp).Select
    ActiveCell.FormulaR1C1 = "=RC[-2]& "" "" & RC[-1]"
    Set rFormula = Range(strCol & "1")
    Set rDestination = Range(strCol & "1:" & strCol & rFormula.SpecialCells(xlLastCell).Row)
    rFormula.Copy Destination:=rDestination
    rDestination.NumberFormat = "@"
    rDestination.Value = rDestination.Value 'convert all but first row to values

End Sub

Thanks again all for sharing your wisdom.

sawilliams

 

[SkipVought]

'
    strCol = InputBox("Enter Column Letter to the RIGHT of the 2nd Address Line Column", "Concatenate Address Lines", "A")
    
    If strCol <> "" Then

        Columns(strCol).Select

        Selection.Insert Shift:=xlToRight
        Selection.End(xlUp).Select
        ActiveCell.FormulaR1C1 = "=RC[-2]& "" "" & RC[-1]"
        Set rFormula = Range(strCol & "1")
        Set rDestination = Range(strCol & "1:" & strCol & rFormula.SpecialCells(xlLastCell).Row)
        rFormula.Copy Destination:=rDestination
        rDestination.NumberFormat = "@"
        rDestination.Value = rDestination.Value 'convert all but first row to values
    End If

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

 

[sawilliams]

Wow, thanks Skip. Excellent!

 

[SkipVought]

Here's how you could discover what was happening:

In the VBE, put a BREAK on the statement that was causing the error.

Run the procedure.

Hit the CANCEL button.

The VBE is activated. Hover your cursor over the strCol variable and you will see the value of that variable in a popup.

Now you can determine what to do to avoid that value being used in the remainder of your code that uses that variable.



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

 

[Andrzejek]

All those:

Selection.Replace What:=" Drive ", Replacement:=" Dr ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
...

You could replace with just a simple loop that reads from a sheet in Excel.
Let's say you have this on Sheet3:

[pre]
A B
TakeThis ReplaceWith
" Drive " " Dr "
" Drive" " Dr"
" Dr. " " Dr "
" Dr." " Dr"
" Road " " Rd "
" Road" " Rd"
" Rd. " " Rd "
" Rd." " Rd"
...
[/pre]
So instead of all the values in a lot of code (bad idea), you have a simple loop (you know how to loop down and read data in Excel by now ) and it is easy to add new 'stuff' to be replaced with other 'stuff'. And you can easily see what you have and what you don't.

So your looong code would look something like this (a bit shorter):

iRow = 2
Do While Sheet(3).Range("A" & iRow).Value <> ""
    Selection.Replace What:=Replace(Sheet(3).Range("A" & iRow).Value, Chr(34), "") , Replacement:=Replace(Sheet(3).Range("B" & iRow).Value, Chr(34), "") , LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    iRow = iRow + 1
Loop

If you leave " with your data so you can see what's what, you can always get rid of them in code

But I would reconsider the idea of fixing your data in CSV (text) file before you dump it into Excel. You can do all of that in your Excel's VBA.

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]

Thanks for the tips. So, the loop suggestion sounds much smarter than my lengthy code. The file that I'm presently opening in Excel is a CSV that I run my macros against and it remains a CSV when I'm done. One of my databases has customer numbers that appear to be dates like 02-06-2312 and Excel likes to change those to look like this 2/6/2312. That screws me up later when I'm trying to tie accounts out by customer number. So, keeping it as a CSV is necessary so the text fields don't convert to dates. So, can I keep the "TakeThis" and "ReplaceWith" data in a separate spreadsheet altogether and reference it in the VBA in the CSV file I download from online? Or do the Take and Replace values need to be in the same spreadsheet, meaning it has to be an XLSX ?

 

[SkipVought]

I make it a practice to NEVER (well, hardly ever) open a text file with Excel. A .csv file is a text file.

Rather, I IMPORT a text file into Excel, via Data > Data tools > Text to columns. In this process, which can be performed in VBA for files with identical column formats, you can specify exactly how you want the import to occur. In the case of your Customer Numbers, the specification can be TEXT.

Check out faq68-7375.

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

 

[Andrzejek]

The file that I'm presently opening in Excel is a CSV that I run my macros against and it remains a CSV when I'm done.

That is my point: you start with a text file, run it thru Excel (which takes "customer numbers that appear to be dates like 02-06-2312 and Excel likes to change those to look like this 2/6/2312."), and save it as a text file at the end.

I am sure Skip's suggestion to IMPORT the data into Excel with full control of what happens to data will work.

My suggestion is: use Excel's VBA, but open CSV file, fix the data in it, save it back as CSV file without dumping the data into Excel.

Something like:

Dim strTextLine As String
Open "C:\Temp\MyDataFile.csv" For Input As #1
Open "C:\Temp\MyNewDataFile.csv" For Output As #2

Do While Not EOF(1)             [green]' Loop until end of file.[/green]
   Line Input #1, strTextLine   [green]' Read line into variable.[/green]

   [green]'Modify, fix data, do what you want[/green]

   Print #2, strTextLine
Loop
Close #1

Close #2
Close #1

The good part is: you can re-use pretty much all of your code that you already have to do it.

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]

Guys:

Actually I do import the data into Excel and, yes, I specify the data type to be text for my problematic date-like ID fields. And I never save it as an XLS but keep it as a CSV. Still, even after taking that precaution, when I link the CSV in Access (as a linked table), some of the "text" ID's still revert to dates even tho' the data type in Access is text as well. It has been driving me crazy for a long while because 1) it is arbitrary -- not all the date-like ID's turn to dates and 2) I can't tell at what point the conversion to date happens. However, I have begun the process of converting all these annoying date-like ID's to something utterly un-date like so "02-06-2312" becomes "A02062312". It's a long process since many of these accounts are actively being worked with by users so I have to do my update when they lie fallow for a bit.

And, thanks, Andy, for the nifty code to use an external datafile. I will try that today.

Can't tell you how much I appreciate the great help I get here.

--Stephen

 

[Andrzejek]

sawilliams,
Give it a shot. You may have totally different look at verifying data in a text file, and you may never look back into doing it in a Sheet in Excel, who knows...

I would start with a simple 3 column CSV just to get the hold of things, like Split() and Join() functions (very helpful). And VBA in Excel is just the language to use. You also may do it in C#, VB.NET, etc. Your call

And since we talk code for a log time here, I would start a new thread at forum707 And don't worry, Skip and I will meet you 'on the other side' to help

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.