Macro Fill Down - filling too much of the column

[Greegan]

I have an address list of companies that come in more times a week from my
customers than I care to work on.
My shipping software doesn't accept 9-digit zip codes for US destinations
and will choke if I don't remove them.
Column H is the Country Code column.
Column I is the Zip/Postal Code column.
Currently I have a macro that I recorded which does the following.
Enters a blank column in Column J (usually contains other data).
Then counts the length of characters in each cell.
Sorts the sheet to Column J in Descending order

That's the macro. I then highlight all cells in Column I with 9 or more
characters (as noted in Column J) from the country code of US (Column H) and
do a Fixed Text to Columns to remove the 4 digits on the right.
I do this again with all cells with 8 and then 7 characters.

I format the column as Zip Code and I am done.

My Problem with this:
The way I did the macro was to highlight the whole of Column J and do a fill
down with the "=len(cell)" formula.
This causes a problem as it now suggests we have 60,000 items in Column J.
Being that there could be 5000 addresses one day and 10,000 the next I
didn't know of any other way to do this.

My Question is...

Would it be possible to do all the above in one macro?
If I'm recording a macro, is there a way to have it select the end of the
column when there is a chance it won't be in the same row each time. Sort of
like Ctrl+End but for the column.


Your help is much appreciated.

G

 

[Max]

My Problem with this:

The way I did the macro was to highlight the whole of Column J and do a fill
down with the "=len(cell)" formula.
This causes a problem as it now suggests we have 60,000 items in Column J.
Being that there could be 5000 addresses one day and 10,000 the next I
didn't know of any other way to do this.

My Question is...

Would it be possible to do all the above in one macro?
If I'm recording a macro, is there a way to have it select the end of the
column when there is a chance it won't be in the same row each time. Sort of
like Ctrl+End but for the column.

Perhaps experiment with the Sub InsertFormula() below
in a *spare* copy of your book

The sub assumes the target col is col J (data from row1 down)
It will insert the formula: =LEN(TRIM(J1)) in K1
and fill down col K to the last row of data in col J

This seems your key objective, from the extract of your post above
I added a TRIM() within the LEN() formula to remove
any leading, trailing or extra in-between spaces

If the sub works, think you could then just call the sub
via inserting this line at the appropriate point in your macro:

Call InsertFormula

-----------
Sub InsertFormula()
' Target col is col J. Sub inserts formula
' in adjacent col K(till last data row in col J)

Dim lastrow As Long
Dim sFormula As String

sFormula = "=LEN(TRIM(RC[-1]))"
With Sheets("Sheet1")
lastrow = .Cells(Rows.Count, "J").End(xlUp).Row
With .Range(.Range("K1"), .Cells(lastrow, "K"))
.FormulaR1C1 = sFormula
End With
End With

End Sub