title: Why does excel rowcount return max row, after a change on the sheet?
body: <p>The code works &quot;beautifully&quot;(it needs to be cleaned up etc.) but if I delete rows or modify rows in <code>Worksheets(&quot;Material For Quotation&quot;)</code> and rerun the code it defaults to the max rowcount (1,048,576). I've searched around and read the Microsoft stuff and I cant figure it out. What I am stumped on is that the code should run independent of what's in the worksheet, but the minute I delete or modify the worksheet rowcount doesn't work.</p>
  <p>Does anyone have insight?</p>
  <pre><code>Sub QuoteToMaterial()
  
  Dim wb As Workbook
  Dim ws, wscheck, ws1, ws2 As Worksheet
  
  Dim i, j, k, l As Integer
  Dim dict As New Dictionary
  Dim Str As String
  
  Dim key As Variant
  
  Set wb = ThisWorkbook
  Set ws1 = wb.Worksheets(&quot;Material For Quotation&quot;)
  i = ws1.Cells(Rows.Count, 2).End(xlUp).row
  j = 1
  'init dictionary
  For j = 2 To i
      If dict.Exists(Trim(UCase(ws1.Cells(j, 3).value))) = True Then
          dict(Trim(UCase(ws1.Cells(j, 3).value))) = dict(Trim(UCase(ws1.Cells(j, 3).value))) + ws1.Cells(j, 4).value
      Else
          dict.Add ws1.Cells(j, 3).value, ws1.Cells(j, 4).value
      End If
      
  Next j
  
  i = wb.Worksheets.Count
  j = 1
  
  For k = 1 To i
      Set wscheck = wb.Worksheets(k)
      Select Case True
          Case wscheck.Name = &quot;Summary&quot;
              GoTo Loopiter 'loop iterate
          Case wscheck.Name = &quot;Material For Quotation&quot;
              GoTo Loopiter
          Case wscheck.Name = &quot;Pricing Summary&quot;
              GoTo Loopiter
          Case wscheck.Name = &quot;Installation 1&quot;
              GoTo Loopiter
          Case wscheck.Name = &quot;Installation 2&quot;
              GoTo Loopiter
          Case wscheck.Name = &quot;Inst Worksheet 1&quot;
              GoTo Loopiter
          Case wscheck.Name = &quot;Inst Worksheet 2&quot;
              GoTo Loopiter
          Case Else
             
          Set ws = wb.Worksheets(k)
          'inserts $ per unit into the right place
          For l = 4 To 101
              
              If dict.Exists(ws.Cells(l, 9).value) = True Then
                  ws.Cells(l, 11).value = dict(ws.Cells(l, 9).value)
              End If
              
          Next l
  
          j = j + 1
       End Select
  Loopiter:
  Next k
  
      
  
  End Sub
  </code></pre>