To retrieve the first column cell in a filtered table.

aggie81 · May 23, 2013

I want to insert the first cell in a filtered table column in the footer of a report. I found a vba code snippet from http://www.cpearson.com/excel/headfoot.htm to do the footer but I don't have a clue on where to begin with selecting the first cell in a filtered column.

Thanks for any help.

Lee

SirJB7 · May 23, 2013

Hi, aggie81!

If used the built-in macro recorder to configure page printer settings you'd get a code that looks like this:

-----

[pre]

Code:

Option Explicit

Sub SetPrintHeaderAndFooter()
&#39;
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = &#34;$1:$1&#34;
.PrintTitleColumns = &#34;$A:$A&#34;
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = &#34;&#34;
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = &#34;aaaaaaaaaaaaa&#34;
.CenterHeader = &#34;bbbbbbbbbbbbbbbbbb&#34;
.RightHeader = &#34;cccccccccccccccc&#34;
.LeftFooter = &#34;dddddddddddddd&#34;
.CenterFooter = &#34;eeeeeeeeeeeeeeeeeeeeee&#34;
.RightFooter = &#34;fffffffffffffffffffff&#34;
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(3.93700787401575E-02)
.BottomMargin = Application.InchesToPoints(3.93700787401575E-02)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintSheetEnd
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = &#34;&#34;
.EvenPage.CenterHeader.Text = &#34;&#34;
.EvenPage.RightHeader.Text = &#34;&#34;
.EvenPage.LeftFooter.Text = &#34;&#34;
.EvenPage.CenterFooter.Text = &#34;&#34;
.EvenPage.RightFooter.Text = &#34;&#34;
.FirstPage.LeftHeader.Text = &#34;&#34;
.FirstPage.CenterHeader.Text = &#34;&#34;
.FirstPage.RightHeader.Text = &#34;&#34;
.FirstPage.LeftFooter.Text = &#34;&#34;
.FirstPage.CenterFooter.Text = &#34;&#34;
.FirstPage.RightFooter.Text = &#34;&#34;
End With
Application.PrintCommunication = True
End Sub[/pre]
-----


You should only use those settings that you need, it's not necessary to keep them all. Regarding your question you could do this:

-----

.RightFooter = ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Cells(1, 1).Value

-----

Just advise if any issue.

Regards!

aggie81 · May 28, 2013

Thanks, there is a lot to take in. I will try to solve it on my end with the information you gave.

SirJB7 · May 28, 2013

Hi, aggie81!

Glad to help you and welcome back whenever needed or wanted.

Regards!

To retrieve the first column cell in a filtered table.

aggie81

Member

SirJB7

Excel Rōnin

aggie81

Member

SirJB7

Excel Rōnin