Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Select next Empty row in a filtered table: Excel VBA

Posted on 2014-02-13
6
Medium Priority
?
941 Views
Last Modified: 2014-11-10
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:
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select

Open in new window

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

Open in new window

but that just returns an application or object defined error

I have also tried:
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).SpecialCells(xlCellTypeVisible).Select

Open in new window

.  That, however, selects all cells EXCEPT for the filtered cells

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
0
Comment
Question by:David Phelops
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 24

Accepted Solution

by:
Steve earned 1000 total points
ID: 39855958
OK, I would suggest converting your table to an actual "Table"
Highlighting the data and using Insert > Table.

Then the table can be manipulated far easier in VBA.
To add a row use:
    Range("Table1").ListObject.ListRows.Add AlwaysInsert:=True

Open in new window


See attached copy of your file.
FilteredTable-Sample.ExpExch.140.xlsm
0
 

Author Comment

by:David Phelops
ID: 39855979
Thanks Steve, that looks like a leap forward in technique.

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
0
 
LVL 10

Assisted Solution

by:broro183
broro183 earned 1000 total points
ID: 39933948
hi David,

I wrote a "AttemptAtARobustLastCellFinder_v4 function" & it was accepted as the solution in this thread. It may help you in your current situation. The function is powered by "Application.CountA" to identify blank or non-blank rows/columns even if the specific cell is hidden, eg by a filter.

If there is no other data below your filtered range, you can probably use my function "as is". If there is data below the range, you could just make use of the concept & use "Application.CountA" within your code.

hth
Rob
0
 

Author Comment

by:David Phelops
ID: 40017349
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.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40432305
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question