Copy only visible cells

[Jonlowery]

I have a macro that emails a selection however it also emails the hidden cells in that selection. I gather I am going to need to use [FONT=&quot]SpecialCells(xlCellTypeVisible) however I have no clue where I am going to have to place it. Any help/guidance you can offer would be greatly appreciated!!! [/FONT]

Sub Send_Range()
On Error Resume Next
ActiveSheet.Range("F12").Select
With Worksheets("Pledge Notifications").AutoFilter.Range
ActiveCell.Value2 = Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
End With
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Set sht = Worksheets("pledge notifications")
Set StartCell = Range("F8")
Worksheets("pledge notifications").UsedRange
LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
sht.Range("F8:N" & LastRow).Select
Selection.Copy
ActiveWorkbook.EnvelopeVisible = True
With ActiveSheet.MailEnvelope
.Item.to = Range("C15")
.Item.cc = "mycc"
.Item.Subject = "mysubj"
.Item.Display
End With
End Sub

 

[Col Delane]

It's a bit hard to work out where to advise you to add the SpecialCells(xlCellTypeVisible) code as without seeing the sheets I'm not clear as to what your code is actually doing, but it has got to be where you're selecting the range to email (sht.Range("F8:N" & LastRow).Select??) as it is that selection that you're wanting to limit to visible cells.

How are the unwanted rows "hidden" - by Format / Hide & Unhide / Hide Rows or by applying a filter?
What is it that you're selecting and then emailing?
Is the range a filtered range?

 

[Jonlowery]

The unwanted rows are hidden via filter, I am selecting some text,merged cells, and filtered data that creates the semblance of an invoice. The range is a filtered range. Currently whenever I run the macro it will display the email box within excel and select the specified range. Then I click send and it sends the specified range. Unfortunately when you go to reply to the sent message all of the unhidden rows/columns appear in the email. When I tired inserting the pastespecial string to the place you specified it now sends the whole sheet, as well as still revealing the hidden columns/values when I try and reply to the sent email. Thank you so much for your help!!!

 

[Col Delane]

I'm still not clear as to what your data looks like before applying the filter, what is looks like after applying the filter, and what data within the filtered range you're selecting to go into the email. (I must admit that I very little about using VBA to generate and populate an email from Excel)

Are you able to post a screenshot?

1. Download and instal the Forum Tools add-in from the Guideline for Forum Use post on this site at https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html
2. Go to section B(ii) and click the link Forum Tools Add-In (updated 2017) and then download the file and instal the add-in
3. Copy the target range from your spreadsheet, click the right hand button on the Forum Tools tab, confirm the selections, then goto the Forum post, right click at the insert point and click paste [don't use Ctrl+V])

 

[Michael M]

It looks like you are selecting the Usedrange to copy, which will include hidden rows.

Maybe this ....UNTESTED

Sub Send_Range()
Dim sht As Worksheet
Dim LastRow As Long, LastColumn As Long, StartCell As Range
On Error Resume Next
Set sht = Worksheets("pledge notifications")
sht.Range("F12").Select
    With sht.AutoFilter.Range
        ActiveCell.Value2 = Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
        .SpecialCells(xlCellTypeVisible).Copy
    End With
ActiveWorkbook.EnvelopeVisible = True
    With ActiveSheet.MailEnvelope
        .Item.to = Range("C15")
        .Item.cc = "mycc"
        .Item.Subject = "mysubj"
        .Item.Display
    End With
End Sub