Excel VBA循环选择工作表 [英] Excel VBA looping through select worksheets

问题描述

我正在尝试编写一个宏，它将循环选择量的工作表，以隐藏每个工作表上的空行。每个工作表中的A列包含1或0.如果是0，我想隐藏该行。

这是我已经从各种网站报废的代码。我最大的挑战是知道我需要操纵哪些对象。

 在此输入代码
公共Sub HideRows（）
 Dim beginRow As Double 
 Dim endRow As Double 
 Dim ChkCol As Double 
 Dim RowCnt As Double 
 Dim ws As Worksheet 
 Dim ArrayOne As Variant 
 Dim InxW As Long 
 
 beginRow = 10 
 endRow = 185 
 ChkCol = 1 
 
 ArrayOne = Array（GB，Adj。B，Adj。F，JC-Results ，PI结果，MK结果，TD结果）
 
 
对于InxW = LBound（ArrayOne）到UBound（ArrayOne）
对于RowCnt = beginRow To endRow 
如果单元格（RowCnt，ChkCol）.Value = 0然后
单元格（RowCnt，ChkCol）.EntireRow.Hidden = True 
 Else 
单元格（RowCnt，ChkCol） .EntireRow.Hidden = False 
结束如果
下一个RowCnt 
 
下一个
 
 
 
 End Sub 
  

解决方案

尝试这样：

  Public Sub HideRows（）
 Dim beginRow As Double 
 Dim endRow As Double 
 Dim ChkCol As Double 
 Dim RowCnt As Double 
 Dim ws As Worksheet 
 Dim ArrayOne As Variant 
 Dim InxW As Long 
 
 Application.ScreenUpdating = False 
应用程序.EnableEvents = False 
 Application.Calculation = xlCalculationManual 
 
 beginRow = 10 
 endRow = 185 
 ChkCol = 1 
 
 ArrayOne = Array GB，Adj。 B，Adj。 F，JC结果，PI结果，MK结果，TD结果）
 
 
对于InxW = LBound（ArrayOne）到UBound（ArrayOne ）
 With Sheets（ArrayOne（InxW））
对于RowCnt = beginRow To endRow 
如果.Cells（RowCnt，ChkCol）.Value = 0然后
 .Rows（RowCnt）。 Hidden = True 
 Else 
 .Rows（RowCnt）.Hidden = False 
 End If 
 Next RowCnt 
 End with 
 
 Next InxW 
 
 Application.ScreenUpdating = True 
 Application.EnableEvents = True 
 Application.Calculation = xlCalculationAutomatic 
 
 End Sub 
  

主要的问题是你不是告诉Excel哪个工作表进行搜索，所以它只是从代码开始只搜索活动工作表。 / p>

通过将所有内容放在中，使用块并使用。在所有范围对象前面会告诉excel要使用哪个工作表。

同时转动o计算，屏幕更新和事件关闭将有助于加快代码，因为它不会暂停做这些事情。

I'm trying to write a Macro that will loop through select amount of worksheets to hide empty rows on each sheet. In column "A" on each worksheet contains a 1 or a 0. If it's a 0 I want to hide the row.

Here's my code that I've scrapped together from various websites. My biggest challenge is knowing which objects I need to manipulate.

enter code here
Public Sub HideRows()
Dim beginRow As Double
Dim endRow As Double
Dim ChkCol As Double
Dim RowCnt As Double
Dim ws As Worksheet
Dim ArrayOne As Variant
Dim InxW As Long

beginRow = 10
endRow = 185
ChkCol = 1

ArrayOne = Array("GB", "Adj. B", "Adj. F", "JC-Results", "PI-Results", "MK-Results", "TD-Results")


For InxW = LBound(ArrayOne) To UBound(ArrayOne)
    For RowCnt = beginRow To endRow
    If Cells(RowCnt, ChkCol).Value = 0 Then
        Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    Else
        Cells(RowCnt, ChkCol).EntireRow.Hidden = False
    End If
    Next RowCnt

Next



End Sub

解决方案

Try this:

Public Sub HideRows()
Dim beginRow As Double
Dim endRow As Double
Dim ChkCol As Double
Dim RowCnt As Double
Dim ws As Worksheet
Dim ArrayOne As Variant
Dim InxW As Long

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

beginRow = 10
endRow = 185
ChkCol = 1

ArrayOne = Array("GB", "Adj. B", "Adj. F", "JC-Results", "PI-Results", "MK-Results", "TD-Results")


For InxW = LBound(ArrayOne) To UBound(ArrayOne)
    With Sheets(ArrayOne(InxW))
        For RowCnt = beginRow To endRow
            If .Cells(RowCnt, ChkCol).Value = 0 Then
                .Rows(RowCnt).Hidden = True
            Else
                .Rows(RowCnt).Hidden = False
            End If
        Next RowCnt
    End With

Next InxW

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

The main issue is that you are not telling Excel which sheet to search so it is searching only the active sheet as of the start of the code.

By putting everything in a With block and using the . in front of all range objects will tell excel which sheet to use.

Also turning of the calculations, screen updating and events off will help speed up the code, because it will not pause to do those things.

这篇关于Excel VBA循环选择工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！