0

I have an excel sheet that I have to extract data from. Is there a way to select all the cells that have data in them? I'm extracting data right now like so:

Set rng = Selection
newValue = rng.Cells(someNumber, anotherNumber).Value

But this requires me to manually select the cells I want to extract data from.

Also, the range will change from document to document. Is there a way to make this dynamic so I don't have to manually change it for every new document?

Community
  • 1
  • 1
codedude
  • 6,244
  • 14
  • 63
  • 99
  • 2
    Find the last row and the last column and then make your range as shown [HERE](http://stackoverflow.com/questions/30728154/how-to-select-full-range-in-excel-vba/30728355#30728355) – Siddharth Rout Jun 09 '15 at 17:24
  • Depends on the "data". Take a look at what `Range.SpecialCells(...)` allows you to do. It will give a `Range` of cells with `Constants`, `Formulas`, etc. It is the VBA version of the `Go To-> Special` menu (hit `F5` to get that) – Byron Wall Jun 09 '15 at 17:24
  • [This would be relevant](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) – Raystafarian Jun 09 '15 at 17:25
  • In anyway you must to iterate with loops your sheet once you retrieved the limits. – Trimax Jun 09 '15 at 21:54
  • It's not clear how do you want to select the cells. Do you want to select the range containing all cells with data, or do you want to iterate through all cells with data? anyway I think first step should be `Worksheet.usedrange`, it's the range containing all data, as suggested from your title. If you need just the cells with data, then iterate through this range and skip blank cells. – Máté Juhász Jun 10 '15 at 04:36

1 Answers1

1

Its a bit dirty but the easiest way is to do Ctrl+Up to find bottom and ctrl+Left from last column to find right hand side and the code equivalent is:

Range("a1", Cells(Range("a1000000").End(xlUp).Row, Range("xfd1").End(xlToLeft).Column)).Select
AndrewT
  • 468
  • 1
  • 8
  • 23