How do i print only filled rows with a range to a mail |excel | vba|

Question

As the data is generated from data base using SQL

I want to display certain row of data which are filled within a range

As data is fetched dynamically from the Database the data may vary each time I generate

example: - below is the data got fetched from database might contain some rows which are empty

[Data][1]: https://i.stack.imgur.com/ysV64.png

So the issue is how do i identify with in a range how many rows are filled only those rows need to be displayed to the email body (Range is A2 : D16)

My code

Private Sub Mail_0_Click()
'Working in Excel 2000-2016
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object

    Set rng = Nothing
    On Error Resume Next
    'Only the visible cells in the selection
    Set rng = Selection.SpecialCells(xlCellTypeVisible)
    'You can also use a fixed range if you want
    Set rng = Sheets("Sheet").Range("A2:D16").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected" & _
               vbNewLine & "please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = "example@.com"
        .CC = ""
        .BCC = ""
        .Subject = "Student Details"
        ''.HTMLBody = RangetoHTML(rng)
        .HTMLBody = "Hi," _
         & vbNewLine & vbNewLine & _
         "Please find your details " & _
         RangetoHTML(rng)
        .Send   'or use .Display
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub


Function RangetoHTML(rng As Range)
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With

    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.readall
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")

    'Close TempWB
    TempWB.Close savechanges:=False

    'Delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing

End Function

The output should be : only filled rows within a range in a mail body like below

[Output][1]: https://i.stack.imgur.com/oqSLJ.png

I suggest looking at [this answer](https://stackoverflow.com/a/3628198/11936678) to a similar question to determine which rows in your range are used. Then take the output from that to determine a conclusive range. However if the empty rows are always at the end as per your example above, I suggest changing your selected range based on a [lastrow statement](https://stackoverflow.com/questions/38882321/better-way-to-find-last-used-row/38882823#38882823) — Plutian, Oct 23 '19 at 07:59
From what I can see, you don't have any empty rows within your data, just at the end. You could use `UsedRange` to get your range.. if my above presumption is correct — Zac, Oct 23 '19 at 08:46
@Plutian as I am generating dynamically. The data may vary every time so I need to find range of rows filled with the given range 'A2:D16' . This A2:D16 will be constant range does not going to change only within that range I need to find the range of filled data range — , Oct 23 '19 at 09:53
@Zac - as I am generating dynamically. The data may vary every time so I need to find range of rows filled with the given range A2:D16 . This A2:D16 will be constant range does not going to change only within that range I need to find the range of filled data range — , Oct 23 '19 at 09:53

score 0 · Answer 1 · answered Oct 23 '19 at 10:35

The below loops over all rows in your range, and determines whether there is any values within the row. It then sets the range to include all rows which have data.

Sub rngsel()
Dim rng As Range, cel As Range

For Each cel In Sheets("Sheet1").Range("A2:A16") 'loop over the first column in your range

    If Not Application.CountA(Range("A" & cel.Row & ":D" & cel.Row)) = 0 Then 'test if the entire row for each cell has any values with countif

        If rng Is Nothing Then  'test if this is the first row with values
            Set rng = Range("A" & cel.Row & ":D" & cel.Row) 'set the first row in the range
                        Else
                Set rng = Union(rng, Range("A" & cel.Row & ":D" & cel.Row)) 'add the found row to the already defined range

        End If

    End If

Next cel
End Sub

How do i print only filled rows with a range to a mail |excel | vba|

1 Answers1