I have an application where I need to clear some sheets for testing purposes. In the early days I simply used to manually select the rows I needed cleared, and then (using the ribbon) DeleteSheetRows. Environment is XP SP3, Office Professional 2010.
I now want to be able to clear these sheets programmatically, so I am using the following code:
When I run this, I get the following result (in the Immediate window)Code:Private Sub cmdResetTransactions_Click() ' Clear the entries in the Transactions worksheet Dim sheet As Worksheet Dim OldRange As String Dim UsedRange As String Dim i As Integer Dim LastRow As Integer Dim LastCol As String Set sheet = ActiveWorkbook.Worksheets("transactions") OldRange = sheet.UsedRange.Address Debug.Print "Used range for " + sheet.Name + " on entry is " + OldRange LastRow = Utilities1.GetLastRow(OldRange) LastCol = Utilities1.GetLastCol(OldRange) Debug.Print "Old range for " + sheet.Name + " is " + OldRange i = InStr(OldRange, ":") OldRange = "$A2:" + "$" + LastCol + "$" + Format(LastRow) sheet.Range(OldRange).EntireRow.Delete UsedRange = sheet.UsedRange.Address Debug.Print "Reset " + sheet.Name + " from " + OldRange + " to " + UsedRange End Sub
I know that UsedRange is considered flawed, but I'm fascinated where it is picking up the $G$1:$J$1 address! That is (quite possibly) from a completely separate worksheet, and has nothing whatever to do with the one I'm trying to clear....Code:Used range for Transactions on entry is $A$2:$P$10 (this is actually correct. Generated by using UsedRange...) Old range for Transactions is $A$2:$P$10 Reset Transactions from $A2:$P$10 to $G$1:$J$1
I've been beating my head against this for about 36 hours, and am finally conceding defeat. Does anyone have any ideas?
Thanks,
Tony
Reply With Quote
Bookmarks