Select and copy a range with values and not with formulas

germanpbv

New Member
Joined
Jan 25, 2020
Messages
4
Office Version
365, 2019, 2016, 2013
Platform
Windows, Web
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.

help with macro.PNG


Code:
VBA 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!
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

germanpbv

New Member
Joined
Jan 25, 2020
Messages
4
Office Version
365, 2019, 2016, 2013
Platform
Windows, Web
Thank you for your help!!

I copied your line code but I got this error:

vberror.PNG


May be this image can explain better what I need:
Captureemail.PNG


Thanks again!!
 

germanpbv

New Member
Joined
Jan 25, 2020
Messages
4
Office Version
365, 2019, 2016, 2013
Platform
Windows, Web
I was able to save this problem with other post in this forum:


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

VBA Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,163
Messages
5,442,772
Members
405,195
Latest member
Mike Neal

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top