VBA to select values

[KenC53]

I have a macro (Sub SelectData) that selects a range going down from A2 to the last row with values and the over right to the last column with values. The code selects all cells with formulas, even those returning no value leaving the cell blank. how do I change the macro to slect only the cells that have values returned and not those with only formulas entered. The last cell in column A with values is row A96 but this macro selects down to row 150 because there are formulas but no values present. Here is the macro:

ActiveSheet.Range("A2:" & ActiveSheet.Range("A2").End(xlDown). _
End(xlToRight).Address).Select

Ken <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->

 

[Tom Urtis]

Why are you selecting them in the fist place, maybe you can just manipulate those cells in some other fashion without needing to select them, as is usually the case.

 

[KenC53]

I am selecting to copy and paste in another spreadsheet and trying to do as much of it with a macro as possible. The columns are always the same but the rows with values will change from day to day.

 

[Tom Urtis]

Well, one sequence you can employ is to loop through each formula-containing (specialcell #3 constant for formulas as I write it) cell in column A (column 1) and copy that cell, then paste or do whatever to it, per this syntax:


Dim cell As Range
For Each cell In Columns(1).SpecialCells(3)
If Len(cell.Value) > 0 Then
cell.Copy
'paste to wherever
End If
Next cell

 

[KenC53]

That copied the last value, cell A96 in column A. I need to copy A2:I96. The last column will always be column I. The last row of data, in this case Row 96 will change from day to day. Tomorrow it may be A2:I99. Depending on the number of people working.

 

[Tom Urtis]

Which column has the formulas (I assumed it was A because you mentioned that column in your first post), and even though the count of rows changes, is the range of columns from A to I or is the range of columns among some other pair of columns?

 

[KenC53]

All columns A:I have formulas. If they don't return a valuefrom another sheet, they return "".

 

[Tom Urtis]

OK, so that is a 9-column list.

Is it that you want, for example, range A8:I8 to be copied if any, that is even one, of the nine cells in that range do not show a value, because all cells in that range contain formulas that might return conditional null strings. So if for example cell D8, and only D8, does not show a value, A8:I8 gets copied.

Or is it something else you are doing, if so, what.

 

[KenC53]

Whenever Column A has a value returned I need to copy Columns A:I regardless. If there are cells in the row that do not return a value it would be columns F:I but they still need to be copied. I will then go to another sheet and paste the values after copying.

 

[Tom Urtis]

Then just modifying my first code should do it:

Application.screenUpdating = False
Dim cell As Range
For Each cell In Columns(1).SpecialCells(3)
If Len(cell.Value) > 0 Then
Range(Cells(cell.row, 1), cells(cell.row, 9)).Copy
'paste to wherever
End If
Application.CutCopyMode = False
Next cell
Application.screenUpdating = True

regarding this you wrote earlier:

That copied the last value, cell A96 in column A.

Maybe you are saying that because the code is copying each range in turn. I wrote

'paste to wherever

for the purpose of you inserting code to tell each range where to be copied or what to be done with it.
<!-- / message --><!-- sig -->