Clearing UsedRange Of WorkSheet Programmatically
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:
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
When I run this, I get the following result (in the Immediate window)
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 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....
I've been beating my head against this for about 36 hours, and am finally conceding defeat. Does anyone have any ideas?
Thanks,
Tony
