David Phelops
asked on
Select next Empty row in a filtered table: Excel VBA
Help, help, help. I'm driving myself crazy!
I have a table of data which several people use.
I have developed a process to add rows of data at the bottom of the table.
If the table is left filtered by the last user, the code that I use to select the next row does not work.
(I know there are work-arounds, such as ShowAllData, or remove autofiltermode. I would like to know if there is a way to do it without resorting to that, though, keeping the integrity of the filter.)
My code:
However, if the data is fitered, it will obviously only work if the bottom row is included in the filter.
I have tried the following:
I have also tried:
I am missing something obvious, perhaps. Can't see the wood for the trees.
Sample table attached
Thanks very much
David
FilteredTable-Sample.ExpExch.140.xlsm
I have a table of data which several people use.
I have developed a process to add rows of data at the bottom of the table.
If the table is left filtered by the last user, the code that I use to select the next row does not work.
(I know there are work-arounds, such as ShowAllData, or remove autofiltermode. I would like to know if there is a way to do it without resorting to that, though, keeping the integrity of the filter.)
My code:
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
works fine if the filter is set to show all data. However, if the data is fitered, it will obviously only work if the bottom row is included in the filter.
I have tried the following:
selection.SpecialCells(xlCellTypeVisible).Offset(1, 0).Select
but that just returns an application or object defined errorI have also tried:
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).SpecialCells(xlCellTypeVisible).Select
. That, however, selects all cells EXCEPT for the filtered cellsI am missing something obvious, perhaps. Can't see the wood for the trees.
Sample table attached
Thanks very much
David
FilteredTable-Sample.ExpExch.140.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Apologies for a long absence!
Thanks very much for posting this potential solution. I have to admit that a lot of it is a bit over my head at my stage. I will try and understand what it is doing.
The work-around that I used was to identify the filter and then remove and replace it as necessary, making it easy to find the last row.
Not a robust and technically elegant solution, I grant you, but simple for me!
I do really appreciate the trouble that everyone takes to answer questions, so again an apology for a long absence.
Thanks very much for posting this potential solution. I have to admit that a lot of it is a bit over my head at my stage. I will try and understand what it is doing.
The work-around that I used was to identify the filter and then remove and replace it as necessary, making it easy to find the last row.
Not a robust and technically elegant solution, I grant you, but simple for me!
I do really appreciate the trouble that everyone takes to answer questions, so again an apology for a long absence.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
I am still really intrigued, however, as to how to select the next empty row, if the list is filtered (without converting to a listobject).
Cheers
David