VBA搜索行及其关联的列，如果没有，则隐藏列 [英] VBA searching through rows and their associated columns and hide column if there is nothing

问题描述

我是VBA编程的新手.我想

I am new to VBA programming. I would like to

• 搜索工作表，并在第6行上找到"N"或"TR"
• 然后，对于"N"或"TR"列中的每个单元格
• 如果所有单元格均为空白，则删除/隐藏列
• 如果单元格不是空白，请突出显示空白的单元格

这听起来很简单，但我认为它需要两个for循环.

This sounds easy but I think it requires two for loops.

 Sub checkandhide()    
    Set r = Range("6:6")  
    Rows("7:7").Select  
    For Each Cell In r  
        Selection.Find(What:="N", After:=ActiveCell, LookIn:=xlFormulas, LookAt _  
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _  
            False, MatchByte:=False, SearchFormat:=False).Activate  
        'search for N  
        Application.Run "hidecolumn"  
    Next  
 End Sub  


Sub hidecolumn()
    Dim target As Range
    Dim dwn As Range

    Set dwn = Range(ActiveCell.End(xlDown).Address)
    ActiveCell.Select

    ActiveCell.Offset(6, 0).Select

    For Each Cell In dwn
        If Cell.Text = "" Then Columns.Delete
    Next      
End Sub    

随附的示例电子表格

推荐答案

1. 您不需要两个循环.
2. 您提到要隐藏列，但是您的代码建议您删除它(我保留了隐藏的解决方案)
3. 您没有提到哪个空范围(哪些单元格为空白)来决定隐藏该列-我假设所有内容都位于第11行以下.

4. 这是经过试用和测试并在其中包含一些注释的代码.

1. You don't need two loops.
2. You mentioned you want to hide column but your code suggest you delete it (I kept solution which hides)
3. You didn't mentioned which is empty range (which cells are blank) to decide to hide the column- I assumed everything below 11th row.

4. Here is the code which is tried and tested with some comments inside it.

Sub checkandhide()
Dim r As Range
Dim Cell As Range
'don't run it for the complete row but from first to last cell in it
Set r = Range("A6", Cells(6, Columns.Count).End(xlToLeft))

For Each Cell In r
    'you don't need find if you simply need to check value of the cell
    'let's assume we check for 'N' & 'TR'  but not 'n' or 'tr'
    If Cell.Value = "N" Or Cell.Value = "TR" Then

        'there are few possibilities to check if there is any value below _
        row 11 (?!) in analysed column. I would use this one:
        If Cells(Rows.Count, Cell.Column).End(xlUp).Row < 12 Then
            Cell.EntireColumn.Hidden = True
        End If

    End If
Next
End Sub

这篇关于VBA搜索行及其关联的列，如果没有，则隐藏列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！