return to selected cell after closing filter

[robertvdb]

I have a column with thousands of serial numbers. I apply button 1 to filter the range, based on (part of) the serial number. Works fine.

Then I apply button 2 to undo the filter. However, I then want to return to the previously selected cell, in this example to A15.

Who can help me ?

Thanks.

 

[robertvdb]

after having a short smoke break, I found the solution myself. I guess I was too tired after a weekend of VBA programming...

This is my solution:

on top I put:

Public i, j As Integer

then the code for button 2 (which I named cmdShowAll

Private Sub cmdShowAll_Click()

ActiveSheet.ShowAllData

cmdFind.Visible = True
cmdShowAll.Visible = False

Application.Goto Reference:=Cells(i, j)

End Sub

 

[smozgur]

Thanks for posting your solution. It is a good example declaring a variable in the module scope to be able to assign their values in some procedures and accessing them in other procedures. So, it would be great if you could also post the procedure that actually assigns variables i and j. Then it would be more helpful example to the future readers.

Also, the following declaration:

Public i, j As Integer

declares j as an Integer variable, however, i as a Variant. Same line declaration is correct and acceptable (although I personally don't prefer that for readability purposes). However, each variable should be declared with their type as shown below:

Public i Integer, j As Integer

It won't affect the code since the Variant type is OK in this particular case, but you might be thinking that i is also declared as an Integer when it is used in this form. It is not.

While we are talking about variable types, just to make sure not causing an overflow error, declaring i as Long type would be better choice since row index can be more than 32767. Again, this will probably not affect most applications including yours, but I remember the days right after Excel 2007 (1M rows) that I found myself making this mistake a lot in large worksheets.

 

[robertvdb]

Thanks Smozgur !