1

I have a requirement to apply auto fit for the merged cells, it’s working fine.

I am using Uipath for each data table from input and I am applying VBA the below.

When I run this it takes more time because I’ve (oRange) value totally 40000 rows.

Each row takes more time, can this convert into Vb.net or any other way to run it fast?? Please advise.

Public Sub AutoFitMergedCells(oRange)
      Dim tHeight As Integer
      Dim iPtr As Integer
      Dim oldWidth As Single
      Dim oldZZWidth As Single
      Dim newWidth As Single
      Dim newHeight As Single
    Set oRange = Range(""+oRange+"")
     
      With Sheets("MASTER")
        oldWidth = 0
        For iPtr = 1 To oRange.Columns.Count
          oldWidth = oldWidth + .Cells(1, oRange.Column + iPtr - 1).ColumnWidth
        Next iPtr
       'oldWidth = .Cells(1, oRange.Column).ColumnWidth + .Cells(1, oRange.Column + 1).ColumnWidth
        oRange.MergeCells = False
        newWidth = Len(.Cells(oRange.Row, oRange.Column).Value)
        oldZZWidth = .Range("ZZ1").ColumnWidth
        .Range("ZZ1") = Left(.Cells(oRange.Row, oRange.Column).Value, newWidth)
        .Range("ZZ1").WrapText = True
        .Columns("ZZ").ColumnWidth = oldWidth
        .Rows("1").EntireRow.AutoFit
        newHeight = .Rows("1").RowHeight / oRange.Rows.Count
        .Rows(CStr(oRange.Row) & ":" & CStr(oRange.Row + oRange.Rows.Count - 1)).RowHeight = newHeight
        oRange.MergeCells = True
        oRange.WrapText = True
        .Range("ZZ1").ClearContents
        .Range("ZZ1").ColumnWidth = oldZZWidth
      End With
    End Sub
Lars Strange
  • 555
  • 4
  • 9

0 Answers0