title: Excel VBA - Select a range using variables & COUNTA
body: <p>Excel VBA - Select a range using variables &amp; COUNTA</p>
  
  <p>Hi Staked VBA Kings &amp; Queens, I'm trying to learn Excel VBA. A simple task I would like to do is select all the <em>contagious</em> cells in a report dump I get from sales. Simple i'm sure, but I am a total beginner at VBA.</p>
  
  <p><strong>Ok Report Info:</strong></p>
  
  <p>The report is a set number of columns (31). Although I would like to build a bit of variability into my code to accommodate a change in column numbers.</p>
  
  <p>The report grows by number of rows each week, some times less, sometimes more. But Always starts at cell [<code>A4</code>].</p>
  
  <p>I though of using <code>COUNTA</code> function to count used number of rows, then set that as a variable. Similar with rows.</p>
  
  <p>This is what I came up with, although I get a "<strong><em>Run-time Error '1004': Method 'Range' of object'_Global failed... can anyone help me out"</em></strong>. </p>
  
  <p>For me the key is to learn VBA using task I need getting done. I understand the logic behind my code, but not exactly the write way to write it. If some proposes a totally different code I might get lost. </p>
  
  <p>But I am open minded.</p>
  
  <pre><code>Sub ReportArea()
          Dim numofrows As Integer
          Dim numofcols As Integer
          Dim mylastcell As String
          Dim myrange As Range
  
          Worksheets("Sheet1").Select
          numofrows = WorksheetFunction.CountA(Range("AE:AE"))
          numofcols = WorksheetFunction.CountA(Range("4:4"))
          Set myrange = Range(Cells(4, 1), Cells(numofrows, numofcols))
          Range(myrange).Select
  End Sub
  </code></pre>
  
  <p>P.S I did try read slimier trends but only got confused as the solution where very involved.</p>