VBA从Excel中的空白单元格中删除格式 [英] VBA Remove format from empty cells in Excel

问题描述

我需要一个快速代码来清除所有空单元格的格式.我已经编写了这段代码，但是它太慢了.有什么方法可以使其更快?

I need a quick code to clean the format of all the cells that are empty. I have written this code, but it is too slow. Is there a way to make it quicker?

Sub removeFormatEmpty()
    'Declaration of variables
    Dim sheet As Worksheet
    Dim rcell As Range

    For Each sheet In Worksheets
        sheet.Activate
        'Cells.UnMerge
        For Each rcell In sheet.UsedRange.Cells
            If rcell.MergeCells = True Then
                rcell.UnMerge
            End If
            If rcell.Value = "" Then
                rcell.ClearFormats
            End If
        Next rcell
    Next sheet
End Sub

此代码有效，但是它很慢，因为需要逐个单元地进行.除了具有内容的单元格，是否有办法选择整个范围?

This code works, however it is slow as it needs to go cell by cell. Is there a way to select the whole range except the cells with content?

更新:感谢bobajob和jordan的评论，我已经能够更新代码并使其更快，更优化.这是新代码:

Update: Thank you to the comments of bobajob and jordan I've been able to update the code and make it much more faster and optimized. It is the new code:

Sub removeFormatEmptyImproved()
    Dim sheet As Worksheet
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    For Each sheet In Worksheets
        'sheet.Activate
        sheet.UsedRange.SpecialCells(xlCellTypeBlanks).ClearFormats
    Next sheet

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

所以现在解决了.

推荐答案

• 首先，您无需在合并单元格之前检查单元格 是否已合并.因此，要取消合并 sheet ...

  • Firstly, you don't have to check whether a cell is merged before unmerging it. So to unmerge all cells in sheet...

    sheet.UsedRange.UnMerge
    

  • 您无需先更改工作表就可以激活它

  • You don't need to activate a sheet before altering it

    如评论中所述，您可以使用来一次更改所有单元格

    As mentioned in the comments, you can alter all cells at once by using

    sheet.UsedRange.SpecialCells(xlCellTypeBlanks).ClearFormats
    

  • 将 Calculation 转换为手动，将 ScreenUpdating 转换为false是一种加快大多数VBA代码速度的简便方法！

  • Turning Calculation to manual and ScreenUpdating to false is an easy go-to method to speed most VBA code up!

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    
    ' <other code>
    ' Include error handling so that these are always set back!        
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    

  因此您生成的 Sub 将是

  Sub removeFormatEmpty()
  
      Dim sheet As Worksheet
  
      Application.Calculation = xlCalculationManual
      Application.ScreenUpdating = False
  
      For Each sheet In ThisWorkbook.Worksheets   
  
          sheet.UsedRange.UnMerge
  
          sheet.UsedRange.SpecialCells(xlCellTypeBlanks).ClearFormats
  
      Next sheet
  
      Application.Calculation = xlCalculationAutomatic
      Application.ScreenUpdating = True
  
  End Sub 
  

  加快速度的最后一步是进一步挖掘 UsedRange .记住长期未使用的单元格并远远超出必需的单元格可能会臭名昭著.如果您知道工作表布局，则可能有一种方法可以限制正在使用的范围.

  A final step to speed things up would be to dig into your UsedRange a little more. It can be notorious for remembering long-unused cells and being far bigger than necessary. If you know your sheet layout, there may be a way to restrict the range you are working with.

  在此处查看一些执行此操作的方法:获取实际使用范围

  See some methods for doing this here: Getting the actual usedrange

  这篇关于VBA从Excel中的空白单元格中删除格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！