Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Sorry but I do not agree with Michael's answer.</p> <p>End(xlDown) is the VBA equivalent of clicking <code>Ctrl</code>+<code>Down</code>.</p> <p>Try <code>Ctrl</code>+<code>Down</code> with</p> <ul> <li>an empty column</li> <li>a column with a value in row 1 but no other</li> <li>values in rows 1 and 2</li> <li>values in rows 1, 2, 3, 7, 8, 9, 13, 14 and 15</li> </ul> <p>This will give you an idea of all the different rows, <code>Ctrl</code>+<code>Down</code> might take you to.</p> <p><code>Set newRange = ws.Range("A1").End(xlDown).End(xlDown).End(xlUp).Offset(1, 0)</code> does not necessarily take you to the last used row plus 1.</p> <p>I am surprised <code>Set newRange = ws.Range("A1").End(xlDown).Offset(1, 0)</code> worked with an empty column. <code>Range("A1").End(xlDown)</code> would take you to the bottom row of the sheet then <code>.Offset(1, 0)</code> would try to take you off the sheet.</p> <p>Consider:</p> <pre><code>Dim RowLast As Long RowLast = ws.Cells(Rows.Count, "A").End(xlUp).Row </code></pre> <ul> <li>If column A is empty, RowLast will be set to 1.</li> <li>If A1 has a value but no other cells have values, RowLast will be set to 1.</li> <li>If a number of cells in column A have values, RowLast will be set to the bottom row with a value.</li> <li>If you have a value in the final row, it will be ignored. </li> <li>If you have a value in the final two rows, RowLast will be set to Rows.Count - 1.</li> </ul> <p>I assume you do not have values in the borrom rows. If you do not care if row 1 is left blank with an empty column, then:</p> <pre><code>RowLast = ws.Cells(Rows.Count, "A").End(xlUp).Row Set NewRange = ws.Cells(RowLast + 1, "A") </code></pre> <p>should give the desired result regardless of the current contents of sheet ws. </p> <p>If you do care about row 1 being left blank, experimenting with <code>Ctrl</code>+<code>Down</code> and <code>Ctrl</code>+<code>Up</code> will give you an understanding of the effect of different combinations of values.</p>
    singulars
    1. This table or related slice is empty.
    1. POExcel vba - xlDown
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USTony Dallimore
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POExcel vba - xlDown
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. CO+1 for nicely explaining it. As it is, I have never been in favor of using "ws.Range("A1").End(xlDown)" to set a range. Reason being, It doesn't give (quite understandably) the correct range if there is a blank cell in between. :)
      singulars
      1. PO
      1. USSiddharth Rout
    2. COTony, thanks for ur help. It worked for me. About the first part, why I did it that way, I was following item 18 http://support.microsoft.com/kb/291308 . Surprised to see no easy way to do this. thanks aga.
      singulars
      1. PO
      1. USbsr
    3. CO@SiddharthRout .. or worse the last cell if the row is blank. Looking up is much better, but still a test is needed to ensure the data range of interest is not blank. [`Find` can be easier](http://stackoverflow.com/q/4872512/641067) depending on what is needed
      singulars
      1. PO
      1. USbrettdj
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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