Confused with .EntireRow.Delete

[Gopalakrishnan]

I'm trying to delete certain rows from the table using 'Clear' button, and the delete part of the code would be something like below:

    For i = lngUsdRng To 2 Step -1 'lngUsdRng - is the last row used in my table
        Rows(i).EntireRow.Delete
    Next

This code deletes the rows as expected, but when I'm taking the used row count for next iteration it still return the previous range. i.e., if the last run had 23 rows in my table, after deleting the rows also it returns 23 unexpectedly.

I thought that deleting entire row would remove the formats too, so that next time when I take used range count for row, it would return the current value. But it's not as I thought, so please check and help me in understanding this mysterious STOP. Thanks!

~~ Gopi

 

[MARK858]

How are you defining lngUsdRng?

 

[Carlos Vandango]

Hi Gopalakrishnan,

I believe you would need to redefine lngUsdRng after the row is deleted

Thanks,
CV

 

[Gopalakrishnan]

Thanks both....!

I've figured out the issue, actually I didn't script to save the workbook before going for next iteration.... that's the reason why it took previous range,,,,!

 

[Michael M]

you don't need .EntireRow
and you shouldn't need to save before next iteration....maybe you should post ALL the code

For i = lngUsdRng To 2 Step -1 'lngUsdRng - is the last row used in my table
        Rows(i).Delete
    Next i

 

[Gopalakrishnan]

you don't need .EntireRow
and you shouldn't need to save before next iteration...

Yeah, it's working without .EntireRow too

But wrt saving my workbook, it requires in my case.

Because, I'm using this delete piece of code for CLEAR button after certain updates in RESULTS sheet which includes cells formats like highlight, font bold, and other stuffs (that too in inserted table).

So after deleting the rows, if I go and take used range count without saving my workbook, as default it returns the previous range. That's how it works when I try...! Plz. correct if I'm wrong...

 

[Gopalakrishnan]

Here is the code I'm using to take used range count:

Function lngRowCount(strSheetName As Variant, strCol As Variant) As Long
    ThisWorkbook.Sheets(strSheetName).Activate
    With ActiveSheet
         lngRowCount = .Range(strCol ).SpecialCells(xlCellTypeLastCell).Row
    End With
End Function

 

[Norie]

Perhaps you could use some other method to count the no of rows, using UsedRange isn't really reliable.

 

[Gopalakrishnan]

Perhaps you could use some other method to count the no of rows, using UsedRange isn't really reliable.

Yup, I had other option to take the count as avoiding EMPTY CELLS while taking the rows count. But I had to consider blank cells in between, i.e., if 20 rows are used in my sheet, then there may be 3 or 4 blank cells in between. That's why I opted UsedRange and ensured that CLEAR button making the RESULTS sheet intact for next iterations...!

Plz suggest, if there is any other best way to accomplish my scenario. Thanks!

 

[Norie]

Why do you need to count the rows?