Multiple questions...

itsupikiookami · Oct 1, 2004

Not sure if this exactly the right subforum, but here goes:

I'm going to be manipulating fairly large (though maybe not to some
spreadsheets (well, after they're imported from either TXT or CS
files) - some will need quite a bit of work while others will need onl
minor edits (that I've already figured out how to macro properly).

A little bit of background info on the files: They are store lists tha
need to be converted and cleaned up so that they can be fed into a FedE
machine (to print labels). The biggest list comprises of a little ove
5,000 entries, other lists vary from 1,000+ entries to varying sizes i
between (including the possibility of a list that includes ALL stores)
The store lists change in size on a nearly weekly basis (adding an
removing of stores, and also alterations to current stores but the
won't need any editing aside from what HAS to be done). Typically, th
largest files come from the company servers and those are pretty clea
(since they're limited access files), but custom store lists that com
from the various departments that send ever-changing and frequen
mailings is a tremendous headache.

One of the things that has to be done with any list (in order for th
FedEx machine to properly process the entire file) is delete som
unused fields. That's pretty easy to macro in, but I also have t
concatenate data from one field to another (so that it become
"CompanyName StoreNumber". I've noticed that, in the macro, this i
"Selection.AutoFill Destination:=Range("B2:B101")", but since tha
number will change (depending on new stores or closings) I need that t
be more flexible (for automation purposes). How?

Another thing that needs attention is removal of characters that th
FedEx machine simply CHOKES and dies a horrible death. Normally, al
this has to be done manually, and when there's 1,000 to 6,000 rows t
go through you can imagine how time consuming this process is. Is ther
any way for Excel to (through a macro) remove certain characters?

Yet another thing is removal of empty rows (since the departments ad
them in in a vain attempt to "help", and it's something else that th
FedEx machines DO NOT like).

Also, I'd like to be able to quickly remove certain rows, namely thos
that require different shipping methods, and put those in a separat
file. Sort of like a "Find & Replace" but really a "Find, Cut, Delet
Row, Paste in a new Workbook"...

Lastly, I also receive files that have important data (namel
addresses) in the wrong field - we use two fields: one for the actua
address, and then another for supplemental address data (like "Such
Such Shopping Center"). Unfortunately, sometimes the first field (th
most important) is left blank and the address is in the second fiel
(and it has to be in the first). Is there any way to quickly ru
through the file and detect blank spots in one field and move data fro
the field next to it? (ie, column "A" is blank but column "B" has th
correct address).

I know this is a lot to ask, but any information would be a tremendou
help and save me a lot of time and headache! TIA!

-Jonatha

Norman Jones · Oct 1, 2004

Hi Jonathan,

(1)

I've noticed that, in the macro, this is "Selection.AutoFill
Destination:=Range("B2:B101")", but since that number will
change (depending on new stores or closings) I need that to
be more flexible (for automation purposes). How?

Try:

Dim LastCell as Range

Set LastCell = Cells(Rows.Count, "B").End(xlUp)
Selection.AutoFill Destination:=Range("B2", LastCell)

(2)

Another thing that needs attention is removal of characters that the
FedEx machine simply CHOKES and dies a horrible death. Normally, all
this has to be done manually, and when there's 1,000 to 6,000 rows to
go through you can imagine how time consuming this process is. Is there
any way for Excel to (through a macro) remove certain characters?

Try the following sub:

Sub DeleteExtraneousChars()
Dim Arr As Variant
Dim i As Long

Arr = Array("O", "Z", "Q") '<<<====== REPLACE

For i = LBound(Arr) To UBound(Arr)

ActiveSheet.Cells.Replace What:=Arr(i), Replacement:="", _
LookAt:=xlPart, _
SearchOrder:= _
xlByColumns, _
MatchCase:=False
Next

End Sub

Simply replace the characters in the array with your unwanted list, run the
sub once, done!

(3)

Yet another thing is removal of empty rows (since the departments add
them in in a vain attempt to "help", and it's something else that the
FedEx machines DO NOT like).

Assuming that ONLY your empty rows have blank cells in column A, the
following sub will delete all the offending rows:

Sub DelBlankRows()
Dim Rng As Range
On Error Resume Next
Set Rng = Columns("A").SpecialCells(xlBlanks)
On Error GoTo 0
If Not Rng Is Nothing Then
Rng.EntireRow.Delete
End If
End Sub

(4)

Also, I'd like to be able to quickly remove certain rows, namely those
that require different shipping methods, and put those in a separate
file. Sort of like a "Find & Replace" but really a "Find, Cut, Delete
Row, Paste in a new Workbook"...

Use an autofilter (Data=>Filter=>Autofilter)
Select the relevant column dropdown(s) and select the special criterion.
Cut & paste the filtered rows to another worksheet.
If you are unfamiliar with autofilter, see the excellent, screen-shot
illustrated tutorial by Debra Dalgleish:

http://www.contextures.com/xlautofilter01.html

(5)

Lastly, I also receive files that have important data (namely
addresses) in the wrong field - we use two fields: one for the actual
address, and then another for supplemental address data (like "Such &
Such Shopping Center"). Unfortunately, sometimes the first field (the
most important) is left blank and the address is in the second field
(and it has to be in the first). Is there any way to quickly run
through the file and detect blank spots in one field and move data from
the field next to it? (ie, column "A" is blank but column "B" has the
correct address).

Assuming that you have already deleted blank rows ( see (3) above), run the
following macro which will copy Add2 into columnA , if Add1 is blank:

Sub RestoreAddress()
Dim LastCell As Range, Rng As Range
Dim RCell As Range
Dim i As Long
Set LastCell = Cells(Rows.Count, "B").End(xlUp)
Set Rng = Range("A2", LastCell(1, 0))
On Error Resume Next
Set Rng = Rng.SpecialCells(xlBlanks)
On Error GoTo 0
If Not Rng Is Nothing Then
For Each RCell In Rng
RCell.Value = RCell(1, 2).Value
Next
End If
End Sub

Norman Jones · Oct 1, 2004