Find first row in a table that is not hidden?

Officer_Bierschnitt · #1 10-30-2015, 02:48 AM

Hi,

I have a macro that seems to work fine from A to Z.
I'm now trying to make it more elegant and slimmer.

- The first thing it does, it selects a table (with daily_updated data for only one
day), selects a specific sheet, highlights all the used cells from column A to
BP or so and copies the whole thing to a file called "always_current".
=> I would like to just skip this step as I can just as well generate my formulas
and calculate the values I need directly in that file.
<=> But that file has a number of hidden rows at the top (between rows 1 and
x) which are not counted - I don't know what is in those rows, but that
seems to be correct.
=> The nr. of rows which are hidden varies from day to day so that I wouldn't
know which row to paste the formula I need.

Can somebody tell me if there's a way in VBA to select the first not_hidden row > 1?

Thanks a lot!

Best regards,

Officer_Bierschnitt

Debaser · #2 10-30-2015, 03:26 AM

This is one way:

Code:

Dim rgToPaste as Range
Set rgToPaste = Sheets("some sheet").Range("A2:A" & Rows.Count).Specialcells(xlCellTypeVisible).Cells(1)

Officer_Bierschnitt · #3 10-30-2015, 04:13 AM

Hi Debaser,

what does that do? I don't want to paste anything yet.
The scenario is a bit different from what I thought, I didn't look close enough.
Those rows are not hidden, they are filtered because some cells are not populated.
In their manual count, the people in that dpt. currently do not include those filtered rows, so I also have to disregard those in my macro.

What I do in that worksheet is a COUNTIF() function. Ah - I'll try modifying that and using a COUNTIFS() and specifying as a second condition that the cells in that column must not be empty.

I'll keep you posted.

Debaser · #4 10-30-2015, 04:27 AM

The code assigns the first visible cell in column A to a variable. You can do whatever you want with that variable afterwards. It does not matter whether cells are manually hidden or filtered.

Officer_Bierschnitt · #5 10-30-2015, 05:07 AM

Hi,

many thanks for the help! That code of yours might prove useful going forward - hidden cells are not so uncommon.
In this case, however, they are only filtered for reasons I know now - and further down, there are more lines which are filtered. By use of another formula inbetween and a COUNTIFS, it works fine now.
It's probably not the most elegant or slimmest possible routine, but it works, that is the target.

Best regards,

Officer_Bierschnitt

NoSparks · #6 10-31-2015, 09:42 AM

Perhaps you can glean something from this

Code:

Sub First_VisRowOfTable()
    
    Dim Tbl As Excel.ListObject
    Dim cel As Range
    
Set Tbl = ActiveSheet.ListObjects(1)    'first table on sheet

With Tbl
    For Each cel In Tbl.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)
        MsgBox cel.Row
        Exit For
    Next
End With

End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA Table – Search All Tables - Find & Replace Text in Table Cell With Specific Background Color	jc491	Word VBA	8	09-30-2015 06:10 AM
find 2 values i a table to find the right prise	Vibov	Excel	1	01-11-2015 07:25 AM
programmatically inserting hidden text into a Word 2010 table	epid011	Word VBA	16	12-30-2013 11:29 AM
Hidden text in table	Angel9520	Word Tables	1	05-02-2012 01:00 AM
Hidden style applied over already-hidden text.	christie	Word	1	08-17-2011 09:10 AM