StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>OK, I'm going to refactor your code a bit to make it more readable, and try and answer your question too.</p> <p>Firstly, I believe that <code>lastrow = Worksheets("Sheet1").Rows.Count</code> will return the <strong>number of rows in the worksheet</strong>, not the number of rows you have currently filled in. When I ran that line I got back 1048576! To find the first <em>empty</em> row, we need to find a column that will definitely have a value filled in for each row; then, cycle down that column looking for an empty cell: this is your first empty row:</p> <pre><code>Dim rngSeek as range set rngSeek = Range("A1") <--your starting cell While rngSeek <> "" 'If the cell isn't empty, drop down one row: rngSeek = rngSeek.Offset(1, 0) Wend 'rngSeek is now sitting at the first row that has a blank in column A </code></pre> <p>So this technique will find us the first row we are looking for. Then, we just populate that row with the data. You can leverage other elements from the code snippet above to make things easier when updating: specifically, the <code>.offset</code> method:</p> <p>Your code:</p> <pre><code>With rngSeek.offset(0, 2) With .Font .Name = "Solid Edge ANSI1 Symbols" .Size = 11 End With .value = 1 End With 'repeat for all cells: no need to select them first, just use .offset </code></pre> <p>FYI <code>.Offset</code> goes like so:</p> <pre><code>Range("Cell Reference").Offset(rows, columns) </code></pre> <p>Where 'Cell Reference' can be any cell, like 'A1' or 'D24', rows is the number of rows to offset <em>to the right</em> (use negative number to offset to the left), and columns is the number of columns to offset <em>down</em> (use negative to offset up). So a value of <code>.offset(0, 0)</code> offsets to nowhere.</p> <p><strong>EDIT:</strong> You may not use column A to seek the first blank cell; if so, adapt the <code>offset</code> values to reflect which cell you want to change the value of. A value of 1 will offset one column right or one row down, and -1 will offset one column <em>left</em> or one row <em>up</em>.</p> <p><strong>ADDITION:</strong> It will be better to use <code>Range("A1").End(xlDown)</code> instead of the loop to find the first blank cell. Set the range variable equal to this, and replace the loop with:</p> <pre><code>set rngSeek = Range("A1").End(xlDown).Offset(1, 0) </code></pre> <p>You can then use <code>rngSeek</code> the way described above. (you can rename <code>rngSeek</code> to any variable name also).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload