VBA Select cells with value only

[dexterfly]

I am having a lot of issues trying to select the cells with value only.
For some reason, Excel selects all of the cells with value or blank cells that had a formula before and now they are empty. (see image below)

Here is the formula I am using to select the cells with value:
Sub Sheet3_Rectangle1_Click()
' select all the cells in columns D:G
ActiveSheet.Range("d1").CurrentRegion.Select
Selection.Copy

' paste only the values
Range("d1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' select all the cells in columns D:G
ActiveSheet.Range("d1").CurrentRegion.Select
Selection.Copy

End Sub



In the image below, when I click on the SELECT ALL button, it selects all the cells with no value that has a formula before.
I WANT TO AVOID IT. I need to select all columns and rows with value. I need to stop at the last row with actual value.
In the example below, it should stop at row 18 instead of row 1000 that had a formula before the copy and paste process.

My goal is to select D1 to G:18. Right now is selecting D1 to G:1000.

I need help fixing this issue. Thanks

 

[JoeMo]

Maybe:
Range("D1:G1000).SpecialCells(xlCellTypeConstants).Select

 

[JLGWhiz]

Dim lr As Long
lr = ActiveSheet.Columns("D:G").Find("*", After:=Range("D1"), xlValues, xlPart, xlByRows, xlPrevious).Row 
With ActiveSheet
.Range("D1", .Cells(Rows.Count, 7).End(xlUp)).SpecialCells(xlCellTypeConstants).Select
End With

 

[dexterfly]

@JoeMo It didn't work because it skipped blank cells in the same row. It also selected the empty cells with formulas.

@JLGWhiz I got an error (syntax error) on the second line of your code

 

[Rick Rothstein]

My goal is to select D1 to G:18. Right now is selecting D1 to G:1000.

I need help fixing this issue. Thanks

Give this single line of code a try...

 Range("D1:G" & Columns("D:G").Find("*", , xlValues, , xlRows, xlPrevious).Row).Select

 

[JLGWhiz]

Just delete the 'After:='

lr = ActiveSheet.Columns("D:G").Find("*", Range("D1"), xlValues, xlPart, xlByRows, xlPrevious).Row
With ActiveSheet
.Range("D1", .Cells(Rows.Count, 7).End(xlUp)).SpecialCells(xlCellTypeConstants).Select

 

[dexterfly]

Thanks @Rick Rothstein It worked!

 

[dexterfly]

ONE MORE THING:

How can I add this to a variable?

Sheets("Body").Range("D1:G" & Columns("D:G").Find("*", , xlValues, , xlRows, xlPrevious).Row).Select