Count the number of visible rows after applying autofilter

[Ron_N]

Hi Team,

I am using the following code and want to apply the below mentioned formula to delete the number of rows: apply the autofilter ( which is in a loop). then after calculating the value of c, i want VBA to delete the number of c rows from the number of visible rows but i want my header intact without any changes to the header--------------


Set dic = CreateObject("Scripting.Dictionary")
Set rngAllValues = Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)
For Each cell In rngAllValues
If Not dic.exists(cell.Value) Then _
dic.Add cell.Value, cell.Value
Next cell
arrUniqueValues = dic.Keys
For i = LBound(arrUniqueValues) To UBound(arrUniqueValues)
'MsgBox arrUniqueValues(i)
Range("A1:W1").AutoFilter field:=4, Criteria1:=arrUniqueValues(i)

a = Sheets("Data_sample").Range("a1", ActiveSheet.Range("a1").End(xlDown)).Count ' i want 'a' to be the count of visible rows not counting the header after applying autofilter

VarRateTable = Sheets("Work Type-allocation").Range("A1:B65536")
vt = Application.WorksheetFunction.Vlookup(Cells(2, 4).Value, VarRateTable, 2, 0)
b = a * vt
ab = Round(b, 0)
c = ab + 1 ' what i am doing here is that i first want to calculate 'c' based on the vlookup values. (well, i don't know whether i should be adding 1 here, but the idea is not to delete the header row)

On Error Resume Next

Rows(lrow + c).Resize(ActiveSheet.Range("a2", ActiveSheet.Range("a2").End(xlDown)).Count - c).Delete

'suppose when i get c=3, then i want the number of visible rows (not counting the header) to minus the number of 'c' rows (in this case 3)

Next i
---------

but i am not getting what i want . My errors are :

Firstly, that i am not getting the correct number of rows with the formula for 'a'.
Secondly, i am not getting the formula correct for deleting rows. in this case i even get my header deleted.

I tried to figure out where i am doing wrong but to no avail.
Please help me out.. this is on an urgent basis.

Thanks Ron

 

[Weaver]

try using this function for visible rows

Function visRows(ByVal headercell As Range) As Long
    visRows = 0
    On Error GoTo nonVis
    visRows = Range(headercell, Cells(Rows.Count, headercell.Column).End(xlUp)).SpecialCells(xlCellTypeVisible).Rows.Count - 1
nonVis:
End Function

called with

c = visRows(range("A1"))

Having said that, I can't escape the feeling there's a better way to do what it is you're trying to do, if we were to start from scratch. If this code doesn't completely solve your problem, try explaining the whole thing from the beginning, ignoring any code you've already written.

 

[Ron_N]

HI Weaver,

Thank you for the quick reply.

Ron