If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Macro Fill Down - filling too much of the column
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 |
#2
|
|||
|
|||
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 ------------- -- Rgds Max xl 97 --- Please respond in thread xdemechanik atyahoodotcom ---- "Greegan" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro command go to last filled cell in column? | PatsyB. | Setting up and Configuration | 1 | May 17th, 2004 08:09 PM |
How do i fill one column based on another? | Debra Dalgleish | Worksheet Functions | 3 | November 19th, 2003 02:24 PM |