VBA定义行高-优化

我在下面创建了代码,该代码定义了行范围的高度,同时检查行是否为空。

It is working as intended, however it´s surprisingly slow. For context the variable length has 64 rows and it´s taking approximately 15s to run.

有谁知道我如何优化此代码?

谢谢

sub linhasdim()

' define a altura das linhas da folha planner

Application.ScreenUpdating = False

Dim i As Integer, n As Integer

Sheets(1).Activate ' activa a sheet(1)

Lastrow = Cells(Rows.Count, "O").End(xlUp).Row ' guarda o indice da ultima linha com conteudo da coluna O. Mesmo havendo vazios identifca a ultima linha

Length = Range(Range("O6"), Range("O" & Lastrow)).Rows.Count ' dimensão da coluna O ate a ultima celula com conteudo começando na O6

For i = 1 To Length ' loop na coluna O

    If Range("O6").Offset(i, 0).Value <> "" Then ' se célula O(i) não tem vazio altura = 20

     Range("O6").Offset(i, 0).RowHeight = 20

    Else ' se for vazio altura =3

    Range("O6").Offset(i, 0).RowHeight = 3

    End If

Next i


End Sub
评论
  • Oo晓oO
    Oo晓oO 回复

    试试这个简单快捷的解决方案。范围内的空单元格的行高设置为20。范围内的文本/常量单元格的行高设置为3。

    Sub linhasdim()
    
    ' define a altura das linhas da folha planner
    
    Application.ScreenUpdating = False
    
    Dim i As Integer, n As Integer
    
    Sheets(1).Activate ' activa a sheet(1)
    
    Lastrow = Cells(Rows.Count, "O").End(xlUp).Row 
    ' guarda o indice da ultima linha com conteudo da coluna O. _
    Mesmo havendo vazios identifca a ultima linha
    
    Range(Range("O6"), Range("O" & Lastrow)). _
    SpecialCells(xlCellTypeBlanks).EntireRow.RowHeight = 20
    
    Range(Range("O6"), Range("O" & Lastrow)). _
    SpecialCells(xlCellTypeConstants, 23).EntireRow.RowHeight = 3
    ' thought I wonder why row height the for cells with text is 3 !!
    Application.ScreenUpdating = True ' better to reset the value
    
    End Sub