VBABEGINER
Well-known Member
- Joined
- Jun 15, 2011
- Messages
- 1,232
Can any one pls help here..
Filter Col AB as a blank values. Select first row after header and give cell name as "False". Then copy this "False" value for rest of the filtered rows.
Filter Col AB as a blank values. Select first row after header and give cell name as "False". Then copy this "False" value for rest of the filtered rows.
VBA Code:
LastRow = Range("A2", Range("A" & Rows.Count).End(xlUp)).Count
Range("AB1").Select
ActiveSheet.Range("$A$1:$AM$" & LastRow).AutoFilter Field:=28, Criteria1:="="
Dim r As Long
r = Range("A2:A" & Rows.Count).SpecialCells(xlVisible)(1).Row
Range("AB" & r).Select
ActiveCell.FormulaR1C1 = "FALSE"
'To get last row number after filter the COl AB
Dim FinalRowFiltered As Long, dataRange As Range
Set dataRange = Range("$AB$2:$AB$" & LastRow)
With dataRange.SpecialCells(xlCellTypeVisible)
FinalRowFiltered = .Areas(.Areas.Count).Row + .Areas(.Areas.Count).Rows.Count
'MsgBox FinalRowFiltered
End With
Range("AB" & r).Select
Selection.Copy
'instead of below line..
Range("AB3:AB" & FinalRowFiltered).Select
'I need in this way
Range("AB"&r+1 ":" "AB" & FinalRowFiltered).Select
Selection.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AB1").Select
ActiveSheet.Range("$A$1:$AM$" & LastRow).AutoFilter Field:=28, Criteria1:="False"