11 Comments

1. thank you I was looking for this code from sometime

   Reply

2. Corrrect the 1st code
   Declare the variable “rInput”.

   Reply

   1. Thanks for your response Sandeep. As we checked rInput is already declared as input parameter of ConvertRangeToHTMLTable function; hence need not to be declared separately.

      Reply

3. Hello – I’ve been working with this but I can’t figure out how to change the header row to bold. I’m using the function for 3 seperate tables on the same sheet and using If rCell.Row = 1 this is the first row in the sheet, not in the range? How do I select the first row in the range?

   Reply

   1. Hi Jason,

      Can you try to replace If rCell.Row = 1 Then with If rCell.Row - rInput.Row = 0 Then.

      Regards
      ExcelSirJi Team

      Reply

4. hello, i copy and pasted your code. in the email, i got a super small table with no code. the problem is rCell.Text is not displaying the text. When i replace rCell.Text with something else, like “hello”, it works perfectly.

   Reply

   1. Hi Gordon,

      We are not able to reproduce the error of small text. The error seems to be more related to data that you are trying to convert in HTML Table. Can you try the same code with any other Excel file?

      Regards
      ExcelSirJi

      Reply

5. Hello – this macro works perfectly thank you! However there are empty rows in my range that I am converting to a table, but when I do the macro the table does not have blank rows. Is there a way I can leave the blank cells as blank rows in the output table?

   Reply

   1. Hi Morgan,

      You can try following code to fix the height of the row in table:

      Public Function ConvertRangeToHTMLTable(rInput As Range) As String
          'Declare variables
          Dim rRow As Range
          Dim rCell As Range
          Dim strReturn As String
          'Define table format and font
          strReturn = "<Table border='1' cellspacing='0' cellpadding='7' style='border-collapse:collapse;border:none'><font size='2' face='Calibri' color='black'>  "
          'Loop through each row in the range
          For Each rRow In rInput.Rows
              'Start new html row
              strReturn = strReturn & " <tr align='Center'; style='height:13.00pt'> "
              For Each rCell In rRow.Cells
                  'If it is row 1 then it is header row that need to be bold
                  If rCell.Row = 1 Then
                      strReturn = strReturn & "<td valign='Center' style='border:solid windowtext 1.0pt; padding:0cm 5.4pt 0cm 5.4pt;height:13.00pt'><b>" & rCell.Text & "</b></td>"
                  Else
                      strReturn = strReturn & "<td valign='Center' style='border:solid windowtext 1.0pt; padding:0cm 5.4pt 0cm 5.4pt;height:13.00pt'>" & rCell.Text & "</td>"
                  End If
              Next rCell
              'End a row
              strReturn = strReturn & "</tr>"
          Next rRow
          'Close the font tag
          strReturn = strReturn & "</font></table>"
          'Return html format
          ConvertRangeToHTMLTable = strReturn
      End Function

      Regards,
      Your Excel Mate

      Reply

6. Hello,

   great, that’s exactly what I was looking for.
   I added a small function for wrap text in the cells.
   BR Karl-Heinz
   Function WrapText(strValue As String) As String
   ‘*Declare variables
   Dim strtxt As String, i As Long, MyValues
   ‘*Split value from Cell with chr(10) to get more lines
   MyValues = Split(strValue, Chr(10))
   ‘*Create temp string with breaks
   For i = LBound(MyValues) To UBound(MyValues)
   strtxt = strtxt & IIf(Len(strtxt) > 0, “”, “”) & MyValues(i)
   Next i
   WrapText = strtxt
   End Function

   Public Function ConvertRangeToHTMLTable(rInput As Range) As String
   ‘*Declare variables
   Dim rRow As Range
   Dim rCell As Range
   Dim strReturn As String
   Dim strValue As String

   ‘*Define table format and font
   strReturn = ” ”
   ‘*Loop through each row in the range
   For Each rRow In rInput.Rows
   ‘*Start new html row
   strReturn = strReturn & ” ”
   For Each rCell In rRow.Cells
   ‘*If it is row 1 then it is header row that need to be bold
   If rCell.Row = 1 Then
   ‘*Check if wrap-text is available
   If InStr(1, rCell.Value, Chr(10), vbBinaryCompare) > 0 Then strValue = WrapText(rCell.Value) Else strValue = rCell.Value
   strReturn = strReturn & “” & strValue & “”
   Else
   If InStr(1, rCell.Value, Chr(10), vbBinaryCompare) > 0 Then strValue = WrapText(rCell.Value) Else strValue = rCell.Value
   strReturn = strReturn & “” & strValue & “”
   End If
   Next rCell
   ‘*End a row
   strReturn = strReturn & “”
   Next rRow
   ‘*Close the font tag
   strReturn = strReturn & “”
   ‘*Return html format
   ConvertRangeToHTMLTable = strReturn
   End Function

   Reply

   1. Thanks for the value addition 🙂

      Reply