-1

I have a spreadsheet where I filter from row 1 column W and then apply a formula to the next visible cell. The next visible cell varies each week. It could be 1160 one week then 1165 the next, etc.

How can I write macro code that will pick up on the next visible cell no matter what row it is. It could be something simple that says "start on E1 and drop down to the next visible cell in column E" because then I don't mind using active cell formula entry.

Does anyone have any ideas? Thank you.

Here's what I have:

 With Sheets("Data").Range.SpecialCells(xlCellTypeVisible)("W1")
      .Value = "My Formula"
 End With
LittleAuditorium
  • 27
  • 4
  • 2
    `Range.SpecialCells(xlCellTypeVisible)` should be helpful. – BigBen Oct 09 '20 at 14:38
  • How would I tell the program to go to the next visible cell though. I've used (xlCellTypeVisible) a lot in the past for copying and deleting rows after filtering. but I don't know the code to put with that to make the cursor go there. – LittleAuditorium Oct 09 '20 at 14:50
  • You don't need to "make the cursor go there" to insert a formula though. [Related](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Oct 09 '20 at 14:51
  • Did what I add in my initial post bring me closer? I used some of the info from your link but I have minimal coding knowledge so I didn't fully understand much of it. How do I get that code to look at Col W? – LittleAuditorium Oct 09 '20 at 15:03

1 Answers1

0

If I understand the problem correctly:

With Sheets("Data").Range("W:W").SpecialCells(xlCellTypeVisible).Cells(2)
    .Formula = "My Formula"
End With
BigBen
  • 46,229
  • 7
  • 24
  • 40