优化VBA代码-基于单元格值的行高 [英] Optimizing VBA codes - row height based on cell values

问题描述

我正在尝试根据单元格值调整行高.该操作必须遍历约700行的过滤数据.

I am trying to adjust the row height based on the cell value. The operation have to run through filtered data with around 700 rows.

下面的代码可以工作，但是需要2-3分钟才能完成操作，这太长了.

The code below works, but it take 2-3 minutes to finish the operation, which is way too long.

我是否有可能无循环执行此操作?或者，请告诉我是否还有其他措施可以缩短手术时间.

Is there any chance I can do this without a loop? Or please let me know if there is anything else I should do to shorten the operation time.

非常感谢您的帮助！

Sub rowheight()

Dim hgt As Integer
Dim WorkRng As Range

Application.ScreenUpdating = False

Set WorkRng = Range("AJ6:AJ700")

For Each C In WorkRng.SpecialCells(xlCellTypeVisible)

    If C.Value > 0 Then
        hgt = C.Value
        C.EntireRow.rowheight = hgt
    End If
Next C

Application.ScreenUpdating = True


End Sub

推荐答案

您可以尝试:

Option Explicit

Sub test()

    Dim i As Long, arr As Variant

    With ThisWorkbook.Worksheets("Sheet1")

        arr = .Range("AJ6:AJ700")

        For i = LBound(arr) To UBound(arr)

            If arr(i, 1) > 0 Then
                .Rows(i + 5).EntireRow.rowheight = arr(i, 1)
            End If

        Next i

    End With

End Sub

这篇关于优化VBA代码-基于单元格值的行高的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！