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 -->

 

[KenC53]

I forgot to add, Column A will always have a value until the first cell in column A that returns a "", after that there will not be an entry in column A.

 

[KenC53]

This returns only one row of data copied. It copies as a link to the data. Is it possible to paste the values instead of the links?

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
Windows("Test.xlsx").Activate
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
Next cell
Application.ScreenUpdating = True

 

[KenC53]

Actually I realized I missed a line. It is working better. Columns A:E are text that is being returned from a cell on another sheet. These are not being copied and pasted correctly with this code. The data in columns F:I is copying and pasting correctly.


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
Windows("Test.xlsx").Activate
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
Application.CutCopyMode = False
Next cell
Application.ScreenUpdating = True