Newbie needs looping help. :)

[krice1974]

Hey there all, thanks in advance for any help to get me over an early vba hurdle. I'm trying to loop through pasted trade confirmations from my broker. Relevant info:
-rows 1-7 are above my dataset area, command buttons etc.
-column 2 is "Buy/Sell".
-It imports cleanly except for misc notes on dividends etc that I wish to delete to just keep a clean dataset of trade confirmations.

Since I only want trade records and want to eliminate other notes etc that paste over from the copied info, looking for either "is empty" or <> buy or sell seems to be the best way to do it. It doesn't work though... it deletes about half of them. When I run it again, it then deletes the remainder. What am I doing wrong???

Here's my code:

Sub Schwab_Trades_Process()

'Click immediately after copying schwab trade confirmations

ActiveSheet.Paste

Selection.UnMerge
Selection.WrapText = False

Dim rowcnt As Integer
rowcnt = Selection.Rows.Count

Dim loopcnt As Integer
For loopcnt = 8 To rowcnt
If IsEmpty(Cells(loopcnt, 2)) Then
Cells(loopcnt, 2).EntireRow.Delete
End If
Next loopcnt

End Sub

Many thanks in advance...

Kevin

 

[LTunnicliffe]

Replace your loop with:

Range("B1:B" & rowcnt).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

HTH,

Loren

 

[krice1974]

Thanks so much, I appreciate the help, but doesn't that delete my rows 1-7? I want to start with 8.

 

[erik.van.geit]

Thanks so much, I appreciate the help, but doesn't that delete my rows 1-7? I want to start with 8.

Hi, krice1974,
WELCOME to the BOARD!!!!!

replace the 1 within the code by 8
you could have tried something on a copy of your file, not?

kind regards,
Erik

 

[Peter_SSs]

Kevin

Welcome to the MrExcel board!

Looping is generally a relatively slow process, so the suggestion that avoids looping is worth pursuing. However, in part answer to your question about your code...

If you are deleting rows in a loop you need to work from the bottom up. The reason is demonstrated by example.

You have a counter to keep track of which row you are up to. Suppose you get to row 12 and it needs deleting so the code deletes it and your row counter moves on to 13. However, because of the deletion, what used to be in row 13 is now in row 12 - but your code is now checking row 13. Therefore what is now in row 12 never gets checked.

Also, if you were checking from, say, rows 8 to 20 and row 12 was deleted, when your counter gets to 20, it will be checking (and maybe deleting) a row that was not even in your original range of rows to be checked.

So your original loop should have started:
For loopcnt = rowcnt To 8 Step -1

I don't think this is why your code is deleting the whole range in two passes but is just a general hint about looping and deleting rows.

 

[krice1974]

Thanks so much for the help! I got a great solution as well as an explanation of why my original code wasn't working. Great stuff...

 

[krice1974]

OK, I'm actually not there yet...

The code below just gives me a blank sheet, deletes the upper row formatting, no records remain etc. Here's what I've got:

Sub Schwab_Trades_Process()

'Click immediately after pasting schwab trade confirmations
'while the whole set is still selected.

ActiveSheet.Paste

Selection.UnMerge
Selection.WrapText = False

Dim rowcnt As Integer
rowcnt = Selection.Rows.Count

Range("B8:B" & rowcnt).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

The help is much appreciated. Thanks in advance.

 

[krice1974]

Sorry. It works now, it didn't as copy/pasted originally. It's still leaving one at the end but I'm far better off for now.

 

[krice1974]

OK, now it doesn't again, I get a "runtime error 1004." All I did between it running and not was put code I was going to try in but after a single quote. I give up for now.

 

[erik.van.geit]

it is not sure what code you are using now??
and please: it might be useful to tell us a bit more about the actual circumstances on your sheet