Select and copy a range with values and not with formulas

[germanpbv]

Hello everyone!! Hopefully somebody can help me!

I am using VBA to create an email from Excel that includes in the body of the message a table that I am selecting as an specific range.

The problem that I have is:

I have not been able to select just the dynamic range that contains values.​

It automatically select all the range that contains also formulas even if the value is empty.​

You can understand better with the image below. Also I am copying the code I am using.

Code:

Sub sentemails()

Dim Outlook As Object
Dim newEmail As Object

Set Outlook = CreateObject("Outlook.Application")
Set newEmail = Outlook.CreateItem(0)

With newEmail
    .BodyFormat = 2
    .Display
    .HTMLBody = Sheet5.Range("B3").Text & "<br>" & Sheet5.Range("B4").Text & "<br>" & Sheet5.Range("B5").Text & "<br>" & .HTMLBody
    
    .To = Sheet2.Range("E1").Text
    .CC = Sheet5.Range("B1").Text
    .Subject = Sheet5.Range("B2").Text
    
    
    Dim xInspect As Object
    Dim pageEditor As Object
    Set xInspect = newEmail.GetInspector
    Set pageEditor = xInspect.WordEditor
    
    Sheet2.Range("A4:Y50").Copy
    pageEditor.Application.Selection.Start = Len(.Body)
    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
    pageEditor.Application.Selection.Paste
    
    Set pageEditor = Nothing
    Set xInspect = Nothing
    '.Send
End With

Set newEmail = Nothing
Set Outlook = Nothing

End Sub

Everything works well creating the email. The problem is the table I am copying brings me all the rows... not only the ones that have data.

Thank you very much!

 

[CSmith]

Like this?

pageEditor.Application.Selection.PasteSpecial Paste:=xlPasteValues

 

[germanpbv]

Thank you for your help!!

I copied your line code but I got this error:

May be this image can explain better what I need:

Thanks again!!

 

[germanpbv]

I was able to save this problem with other post in this forum:

VBA Current Region With Value Not Formula

I have a sheet that contain data from A1:E10. In A2 to A10 i have a formula =if(B2="","",A1+1). So if there is no data in row 2 so on there will not seen the number in column A. Then i try to copy currentregion using vba to select all cell with data but it always select all A1:E10 its because...

www.mrexcel.com

This is the code to select a range that contains values and not the rows with formulas:

Sub GetRange()
  Dim lr As Long
 
  lr = Columns("A").Find(What:="*", After:=Range("A1"), LookIn:=xlValues, SearchDirection:=xlPrevious, SearchFormat:=False).Row
  Range("A1:E" & lr).Select
End Sub

Thanks!