Excel宏在日期输入特定列单元格时自动隐藏行 [英] Excel Macro to auto hide rows when date is enter in specific column cell

问题描述

我很喜欢VBA，而且还在学习。我想要完成的工作似乎很容易，但遇到困难。当日期（任何日期）输入特定列的单元格时，我需要编写一个自动隐藏行的宏。

例如。数据记录在A：F列中。当运送日期输入到G2时，第2行自动隐藏。

任何帮助将不胜感激。

McJ

解决方案

以下VBA代码如果列G中的单元格被编辑并更改为零和当前日期之间的日期（数字），则会隐藏相关行。

  Option Explicit 
 
私人lastSelectedCell作为范围
 
私有子Worksheet_Activate（）
设置lastSelectedCell =范围（A1）
结束子
 
 Private Sub Worksheet_SelectionChange（ByVal Target As Range）
'如果最近选择的单元格在列6（'G'）
 If（lastSelectedCell.Column = 6）Then 
'如果先前选择的单元格是今天或之前的日期
如果（（lastSelectedCell.Value< = Date）和（lastSelectedCell.Value> 0））然后
'隐藏整行
行（lastSele cntCell.Row）.EntireRow.Hidden = True 
 End If 
 End If 
 Set lastSelectedCell = Target 
 End Sub 
  

I am new to VBA and am still learning. What I am trying to accomplish seemed easy enough but am having difficulty. I need to write a macro that auto hides a row when a date (any date) is entered into the cell of a specific column.

E.g. data is recorded in columns A:F. When a "shipped" date is entered into G2, row 2 auto hides.

Any help would be greatly appreciated.

Thanks.

McJ

解决方案

The following VBA code will hide the associated row if a cell in column 'G' is edited and changed to a date (number) between zero and the current date:

Option Explicit

Private lastSelectedCell As Range

Private Sub Worksheet_Activate()
    Set lastSelectedCell = Range("A1")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' If the most recently selected cell was in column 6 ('G')
    If (lastSelectedCell.Column = 6) Then
        ' If the previously selected cell was a date on or before today
        If ((lastSelectedCell.Value <= Date) And (lastSelectedCell.Value > 0)) Then
            ' Hide the entire row
            Rows(lastSelectedCell.Row).EntireRow.Hidden = True
        End If
    End If
    Set lastSelectedCell = Target
End Sub

这篇关于Excel宏在日期输入特定列单元格时自动隐藏行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！