1 -copy vao phan sheet đấy
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E20:E28")) Is Nothing Then 'vùng nhập liệu
AnDong_uyquyenstk 'Tên của Sub AnDong_uyquyenstk()
End If
End Sub
2 - copy vào phần modul
Sub AnDong_uyquyenstk()
Application.ScreenUpdating = False
Dim SoDongDuLieu As Long, DongCuoi As Long, DongDau As Long, SoDongHienThi As Long
SoDongDuLieu = Range("AP1").Value ' noi dat cong thuc tinh so dong co du lieu
DongCuoi = 28: DongDau = 20: SoDongHienThi = DongDau + SoDongDuLieu + 1
If SoDongDuLieu = 0 Then ' neu chua nhap gi ca
Rows(DongDau & ":" & DongCuoi).Hidden = True ' an toan bo
Rows(DongDau & ":" & DongDau + 1).Hidden = False ' hien lai cac dong co chua du lieu
ElseIf SoDongDuLieu = DongCuoi - DongDau + 1 Then ' neu da nhap het cac dong trong hd
Rows(DongDau & ":" & DongCuoi).Hidden = False
Else ' nguoc lai, nhap binh thuong
Rows(DongDau & ":" & DongCuoi).Hidden = False ' hien thi tat ca dong
Rows(DongCuoi & ":" & SoDongHienThi).Hidden = True ' an tu dong cuoi tro len
End If
Application.ScreenUpdating = True
End Sub
3 - công thức counta(AP1)
Sub khac_dandong_bangiaothe()
Sheet55.Select
ActiveSheet.Range("$A$11:$AW$128").AutoFilter Field:=1
End Sub
Sub khac_andong_bangiaothe()
Sheet55.Select
ActiveSheet.Range("$A$11:$AW$128").AutoFilter Field:=1, Criteria1:="<>"
End Sub