Help with Deleting a Row

[Dudley]

Hi,
I'm trying to create some code to extract addresses from an Excel file so they can be imported into Access. We'll have to do this at least once a month on an Excel file formatted this way (and we have no control over the formatting). I don't have any experience with VBA for Excel, and I really only grope my way around in programming Access. I've cobbled some code together by extrapolating from the Help files that almost does what I want it to: go down through the cells in column D and copy and paste/transpose address information when the cell in D equals the cell in C (happens to have a name). There are a lot of extraneous rows, so I want it to delete the row if the cell in D is blank or doesn't equal the value in C. The problem I'm having is that once I delete the row, the active cell moves to the next row which means that when the code loops to the next cell, it ends up skipping a row (the "new" row after the old one is deleted). Can anyone help me figure out how to fix this "skipping" problem? Thanks a bunch!!! (Also, is there a way to determine how many rows it should do this activity to? 250 rows may not always be the right amount.)



Dim cell As Variant

Worksheets("Test").Activate

For Each cell In [d1:d250]

cell.Activate
Select Case cell

Case IsNull(cell)

ActiveCell.EntireRow.Delete​

Case ActiveCell.Offset(0, -1)

Range(ActiveCell, ActiveCell.Offset(3, 0)).Select
Selection.Copy
ActiveCell.Offset(3, 3).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=True​

Case Else

If ActiveCell.Offset(0, 3) = "" Then ActiveCell.EntireRow.Delete​

End Select​

Next cell​

End Sub

 

[RV]

I'm trying to create some code to extract addresses from an Excel file so they can be imported into Access.

I guess you basically only would like to insert new addresses.
Create a temp table in Access.
Import your Excel data into this table.
Next, run a query that imports all rows into your source table in Access.
Add conditions to your query so that:

- addresses already in your source table will not be imported
- any rows having a null address in your temp table will not be imported

Finally, either drop your temp table or delete all rows from it.

RV

 

[shades]

Howdy. Probably the easiest way to do this is to recalculate the last row.

Dim lngLastRow as Long
lngLastRow = Range("A65536").End(xlUp).Rows

Then use lngLastRow as your counter.

________
Buy extreme vaporizer

 

[HaHoBe]

Hi, Dudley,

I would have liked a sample workbook to figure things out.

I´d preferred to use xlSepcialCells for empty cells in column D and G and do the rest afterward. Anyhow by deleting in a For Each-Loop you will not check for two empty cells following directly as the rows fill up while the counter advances. I usually use a For lngCounter = lngLastrRow to 1 Step - 1 for these occasions (or faster the SpeculaCells-version):

Range("D1:D205").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("G1:G205").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Ciao,
Holger

 

[Dudley]

Hi Everybody,
Thank you so much for your responses!

RV: That, essentially, is what I'm working on. The problem is that my source file in Excel has the addresses stacked in rows and not spread out in columns and has many many extraneous rows. I'm trying to clean up the data for easy importing into Access. Thanks, though, for validating my planned process.

Shades: Thanks for that piece of code. I ran into a conflict because Excel was trying to equate the string value of the cell into my lngLastRow variable. So I navigated into the following and it seems to work:
Range("A65536").End(xlUp).Activate
lngLastRow = ActiveCell.Row


HaHoBe: Deleting the extraneous rows first was a great suggestion. That seems to have fixed it. I hadn't known about the SpecialCells thing at all. How cool. Also, I chose to loop through the rows from the top down instead of the bottom up because of the offsets I already had figured into it.

Once I had it working I discovered that sometimes the addresses in my source file aren't 4 rows high, but sometimes 5. Extending from your suggestions I figured out how to go out and find the proper end of my address (ends in "USA") so the selection for copying/transposing could be variable.

So, it's all working quite nicely now. The only question I have left is whether anyone can tell me how to parse the resulting "city, state zip" data on something like "everything to the left of the comma", "two non-blank characters bracketed by blanks or by the comma on the left and a blank on the right" and "five numbers at the end after a space - which could be a zip+4"? So, I mean parse where there might be varying numbers of characters on either side of a comma.

(I can't believe the people who are providing these data are setting them up this way!!!)

I've attached a sample file if you want to see how it turned out. I'm totally interested in any suggestions for improvement.

Thanks ever so much for your help. I appreciate it very much.

Sincerely,
D

 

[HaHoBe]

Hi, Dudley,

make use of the function Split in VBA? Here´s a sample for the active cell but this procedure could be put into a loop. To check if splitting is possible/necessary InStr may be used:

Option Explicit

Sub DudleySplit()
Dim myArray As Variant
Dim bytCounter As Byte
myArray = Split(ActiveCell.Value, ",")
For bytCounter = LBound(myArray) To UBound(myArray)
  Debug.Print Trim(myArray(bytCounter))
Next bytCounter
End Sub

I slightly disagree - I would still prefer deleting to start at the bottom of the rows and work upwards...
Concerning the last row: maybe you should check column D instead of column A? And it´s only Row instead of Rows like you noted in your code (but still it´s unnecessary to select that cell ). Having Excel2007 as a public beta on hand you should consider Rows.Count instead of a given number like 2^16 or 65536...

lngLastRow = Cells(Rows.Count, 4).End(xlUp).Row

Ciao,
Holger

 

[shades]

Yeah, the Rows.Count is the way to go. I am trying to convert all my references to that. But some habits are hard to break.
________
DODGE DYNASTY SPECIFICATIONS