Sub continuing beyond end of used range

[Dr. Demento]

Can someone help me understand why the code below keeps adding one extra line of data (000000000) to the bottom of my dataset every time it's run and what I need to do to stop it?

Thanks, y'all!

  With ActiveSheet
    .UsedRange  ' ~ Reset used range
    Set rngUsed = rng_ActualUsed()  ' ~~ determine actual used range in each sheet
    Set rngX = Range("A1:" & Cells(1, rngUsed.Columns.Count).Address)  ' ~~ Set for range of header row | cells function gives the last cell in row 1
    
    For Each cell In rngX
      With cell
        Select Case True
        
        Case .value Like "SSN"
          For Each cellx In Intersect(cell.EntireColumn, rngUsed).Offset(1, 0)  ' ~~ confines column of corresponding header
            cellx = WorksheetFunction.Substitute(cellx, "-", vbNullString)  ' remove dashes
            cellx.NumberFormat = "@" ' Format as text -- REQUIRED!!
            cellx = Application.Text(cellx, "000000000")  ' format as text but retain leading zeros
          Next cellx
        End Select
      End With
    Next cell
  End With

 

[LockeGarmin]

Range.Offset moves your Range down by 1 row, it does not shrink it. So A1:A10 would become A2:A11. You need to do something like the following to both move and shrink your range:

Range.Offset(1,0).Resize(Range.rows.count-1, 0)

 

[Dr. Demento]

LockeGarmin,

Thanks so much!! I hadn't thought to apply both simultaneously (I guess I hadn't ever seen it done). I had tried reducing the Offset value, which fixed the addition of a new last row issue but then gorked up the header.

With some tinkering, I found that the code should read:

Range.Offset(1,0).Resize(Range.rows.count-1)

Otherwise, the number of columns in the range get set to zero (thanks to Bill Jelen and MSDN).

 

[LockeGarmin]

Oops! You're right. I was following the pattern in Offset and forgot about the ramifications of using 0 in resize. On another note you can actually just use .Offset(1) instead of .Offset(1,0) to representing moving 1 row down if you wanted. Glad I could help!