0

I am trying to figure out how to use VBA to do the following in Excel versions 2003, 2007, 2010 (hopefully it's the same for all of them): Search for the last cell in column A with a constant value in it. Select all data from A2:I2 to the row with the last constant data value in it from the search above.

For example, if the last row with constant data in column A is A13, then I would want to select A2:I2 through A13:I13.

Community
  • 1
  • 1
user2789311
  • 3
  • 1
  • 2
    (i) what do mean by constant data? (ii) what have you tried? – assylias Sep 17 '13 at 21:45
  • A constant in Excel is data in a cell that is not calculated. For example, I use vlookups in a couple of columns. If I use the SpecialCells.xlLastCell, it will return the incorrect row, because it sees the vlookup formulas as "data". You can see this if you use Find & Select, Go To...Special and select Constants. Excel will only select "real" data, not data that is calculated in some way. – user2789311 Sep 18 '13 at 13:34

1 Answers1

0

Edit

Now 'lastRow' will find the last used cell in column A even if there are blanks up above. This assumes the equations are in other columns as your comment suggested.

Sub SelectRange()
  lastRow = Cells(Rows.Count, "A").End(xlUp).Row

  Range("A2:I" & lastRow).Select
End Sub

See this post for how to find last cell in different versions of excel.

Say you did have to evaluate the cells in column A for formulas versus constants you can use the HasFormula method like this:

Sub SelectRange()
  lastRow = Cells(Rows.Count, "A").End(xlUp).Row

  Do While Range("A" & lastRow).HasFormula <> False
    lastRow = Cells(lastRow - 1, "A").End(xlUp).Row
  Loop

  Range("A2:I" & lastRow).Select
End Sub
Community
  • 1
  • 1
Automate This
  • 30,726
  • 11
  • 60
  • 82
  • This does not seem to work, because I have calculated fields in columns E and F (vlookups), and Excel sees that as "data". Is there a way for me to just look in column A for the last data value in a cell, then select those rows from column A - I? The pseudocode would be: find last cell in column A that has any data in it; now select A2 through last cell in column A with data in it. Now select all columns through column I. For example: if the last data cell in A is at A13, my code would select A2:I2 through A13:I13. Thanks so much for your assistance! – user2789311 Sep 18 '13 at 13:53
  • Is there any blank cells between the A1 and the last cell in column A? I can make a faster routine if there are no blanks. – Automate This Sep 18 '13 at 14:07
  • Who's the man? Portland Runner is the MAN! Thanks for the solution! – user2789311 Sep 18 '13 at 14:40