VBA doesn't seem to update last row used

[RichardatRedditch]

I would appreciate some assistance on this query - it has already caused me enough heartache!

I am using Excel 2013 and I have a spreadsheet which contains about 4,000 rows of clients’ names and addresses. I need to select (for mailing purposes) a subset of these, and have used as the selection code the following snippet:

lngLastRow = Cells.SpecialCells(xlCellTypeLastCell).row
MsgBox "total clients = " & lngLastRow - 1, vbOKOnly

For lngRow = lngLastRow To 2 Step -1 ' OK, now loop through the rows from the bottom up, ' because as rows are deleted the ones below are renumbered

If < selection criteria goes here >
Then
Cells(lngRow, 1).EntireRow.Delete
Else < keep count of items we want >
End if

This works very well for the selection process, but does not seem to get Excel to reset the end of the new dataset. In other words, If for example, I have 4,000 addresses, and select 350, Excel still seems to think that the effective end of the data is at 4,000 rather than 350 rows. Using the spreadsheet as the data source for a Word mailmerge means that I get a Word document with the first 350 labels filled in, and 3,650 blank labels!

Can anyone help me please?

 

[Rick Rothstein]

I would appreciate some assistance on this query - it has already caused me enough heartache!

I am using Excel 2013 and I have a spreadsheet which contains about 4,000 rows of clients’ names and addresses. I need to select (for mailing purposes) a subset of these, and have used as the selection code the following snippet:

lngLastRow = Cells.SpecialCells(xlCellTypeLastCell).row
MsgBox "total clients = " & lngLastRow - 1, vbOKOnly

For lngRow = lngLastRow To 2 Step -1 ' OK, now loop through the rows from the bottom up, ' because as rows are deleted the ones below are renumbered

If < selection criteria goes here >
Then
Cells(lngRow, 1).EntireRow.Delete
Else < keep count of items we want >
End if

This works very well for the selection process, but does not seem to get Excel to reset the end of the new dataset. In other words, If for example, I have 4,000 addresses, and select 350, Excel still seems to think that the effective end of the data is at 4,000 rather than 350 rows. Using the spreadsheet as the data source for a Word mailmerge means that I get a Word document with the first 350 labels filled in, and 3,650 blank labels!

Can anyone help me please?

Try executing a command that uses the UsedRange property... that should reset it. For example,

SomeUnusedVariable = ActiveSheet.UsedRange.Address

After executing the above, the end of data should report correctly.

 

[RichardatRedditch]

Hello.

That works like a dream - very, very many thanks for your help!

RichardatRedditch