assign values next to the last used cell

[sindhuja]

Hello Everyone...

I have used the below coding to get the count of the values using autofilter..

Selection.AutoFilter Field:=5, Criteria1:="In"
CountVisRows

Selection.AutoFilter Field:=5, Criteria1:="Out"
CountVisRows

Sub CountVisRows()

Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range

Dim a As Integer

a = rng.Columns(1). _
SpecialCells(xlCellTypeVisible).count - 1
MsgBox a

End Sub



Now I need to save the count in the different sheet. I should find for the last used cell in a row and assign the count of “in” and “out” in the specific column.
The sheet will of the below format
7/26/09 7/27/09 7/28/09
In Out In Out In Out
5 2 9 3

In the above format last used cell is with the value 3, then the “in” and “out” values should be in the next cell respectively.

-Sindhuja

[nst1107]

Sub CountVisRows()




Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range


Dim a As Integer
Dim lastCell As Long '<<<<


a = rng.Columns(1). _
SpecialCells(xlCellTypeVisible).count - 1
MsgBox a


lastCell = Sheets("YourSheet").Cells(3, Columns.Count).End(xlToLeft).Column '<<<<
Sheets("YourSheet").Cells(3, lastCell) = a '<<<<


End Sub

[Bob Phillips]

As you populate the next always, you could use



Range("A3").End(xlToRight).Offset(0,1).Value = a

[p45cal]

..and on another point, perhaps convert your sub to a function:Function CountVisRows()
CountVisRows = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cou nt - 1
End Functionor dispense with that too, if it's only being called once or twice with the likes of:Sheets("YourSheet").Range("A3").End(xlToRight).Offset(0, 1).Value = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cou nt - 1

[sindhuja]

One more query..

If I need to get the values of autofilter items (count of “in” and count of “out”) in another workbook named “consol” in the sheet “Sheet1” where should I change..
I tried out its showing me object required error..

-Sindhuja

[sindhuja]

Any idea on my request….

-Sindhuja

[Bob Phillips]

Do a count using =SUBTOTAL(103,rng)

[sindhuja]

I got the count and stored in the variable “a”. Now I need to assign the value of the variable to another workbook named “sample”. Attached the sample sheet where in I need to update the count of “In” and “out” daily.

I am able to get the counts and the challenge is am not able to update in the “Sample” workbook.

-sindhuja