0

I have some code that attempts to combine a number of worksheets into 1. The idea is that each of the source worksheets is pasted into the target worksheet in a specified order. The first action to take on the target worksheet is to delete all of the existing rows. After doing this I use the usedrange property of the sheet to add each of the source worksheets to the target. I expect the usedrange to be very small after deleting all of the rows. E.g., it would be useful if the value of worksheet.usedrange.rows.count would be 0 or 1. Instead it has the value from before the deletion of the rows. Code snippet follows - see below. Printing wksXml.UsedRange.Rows.Count yields some number in excess of 4000, which is how big it was after the last failed test.

Is this the correct behavior? If so, I guess I can delete the target worksheet and recreate it. Seems like a really blunt instrument.

    ' PrepareXML
    ' Copy the XML generated From SDL Details:
    '   1. Sensors
    '   2. Turnouts
    '   3. Lights
    '   4. Signal Heads
    '   5. Signal Masts
    '   6. Blocks
    Const strSheets As String = "Sensors,Turnouts,Lights,SignalHeads,SignalMasts,Blocks"
    Dim boolDbg As Boolean
    Dim iRow As Integer
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim wks As Worksheet
    Dim wksXml As Worksheet
    Dim wksA As Worksheet
    Set wksA = ActiveSheet
    Set wksXml = Sheets("SdlXml")
    wksXml.Activate
    Application.Goto reference:=wksXml.Range("$A$1"), scroll:=True
    wksXml.Range(wksXml.Cells(1, 1), wksXml.Cells(wksXml.UsedRange.Rows.Count, 
    wksXml.UsedRange.Columns.Count)).Delete
Perry Sugerman
  • 179
  • 1
  • 11
  • 1
    `UsedRange` is unreliable. See [this answer](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last used cell. – BigBen Dec 02 '19 at 20:14
  • I have a perverse sense of enjoyment and thus enjoyed every minute of reading through the discussion of a problem that I thought was pretty simple, but clearly, ins't. I am indebted to all of the contributors to the the referred to problem. I have decide to proceed in the way that I thought was a little over the top. I will create a new worksheet left of my target worksheet. Then delete the original target worksheet and rename the new worksheet to the name of the target. I will have to keep track of the rows used by when copying each source worksheet into the new target and I will be OK. Thanx – Perry Sugerman Dec 02 '19 at 22:12

0 Answers0