CODEWITHSUNDEEP
excelvba
ingwarus
ingwarus

Reputation: 412

How to set value of hidden/filtered cells in Excel VBA?

I've got rows 3 and 4 filtered out. I'm trying set cells value at once:

range("A2:A5").value = 5

and it only sets value to A2 and A5. Of course I can set cells value one by one, but it's painfully slow.

How can I do that fast and correct?

Funny fact: range("A3:A4").value = 5 works. Is that a bug or something?

Upvotes: 2

Views: 1159

Answers (2)

Rick
Rick

Reputation: 53

Perhaps starting the macro with unhiding the columns end ending the macro with hiding them again. Shoud the solution of Victor not work, let me know and I will write the code for it.

Edit for the code;

Sub Filter()

'Remove existing filter in colmn "2"
    ActiveSheet.ListObjects("Tabel1").Range.AutoFilter Field:=2

'Change row A to value 5
Range("A2:A6").Value = "5"

'Re-activate filter
    'Filter in column 2
    'Only show values stating "Yes"
ActiveSheet.ListObjects("Tabel1").Range.AutoFilter Field:=2, Criteria1:="yes"

End Sub

This works on my table. The able has three columns, named "1", "2" and "3". Column 2 contains either "Yes" or "No" at random (for easy filtering proof).

If I filter on Yes and activate the macro, everything in column A ("1") is now value "5", including hidden rows.

Upvotes: 1

Naresh
Naresh

Reputation: 3034

Try this. It doesn't need a loop through all the cells in the range and fills all the filtered cells, hidden and visible.

Sub test()
Range("A2:A5").FormulaArray = "5"    
End Sub

Upvotes: 2

Related Questions