How to Select last blank cell in the table using macro ?

[Akash kothari]

Hello friend,

In the attached file i have put formula in column C, Below that all the cell are blank in the table.

My question- using Macro how can i select all the visible blank cells under column C within table so that i can apply the same formula ? (Number of Rows in the tabel may Vary on daily basis so selecting range would be a problem)
Any suggestion


Thank Akash

 

[Marc L]

Hi,​

​

if it's a VBA filter, it's better to post also your code …​

​

And no selection needed to apply a formula …​

 

[Akash kothari]

Hi,​

​

if it's a VBA filter, it's better to post also your code …​

​

And no selection needed to apply a formula …​

Hi Marc,
That was just a rough file that i have attahed actually file have large number of rows and columns
Below is the part of Macro that i have created, when i am copying formula in cell C2 and i looking like how we do Do Ctrl+shift+Down, but only witin That table

Range("F13").Select
Selection.AutoFilter
ActiveSheet.Range("$A$2:$H$46").AutoFilter Field:=7, Criteria1:= _
"=NOT SENT & LATE", Operator:=xlOr, Criteria2:="=SENT LATE"

Range("G6").Select --------- > This is same as Column B in attached file
Range(Selection, Selection.End(xlDown)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With

ActiveSheet.Range("$A$2:$H$24").AutoFilter Field:=8, Criteria1:="="
Range("H6").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC[-1]=""sent Late"",RC[-1]=""Not sent and late""),""Writters Delay"","""")" -----> This formula i have appliet in C2

Thank
Akash

 

[Marc L]

See this code based upon your sample file :​

    With ActiveSheet.AutoFilter.Range.Columns(3)
        .SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeBlanks).Formula = .Cells(2).Formula
    End With

Easy to adapt ! (column should be 8 …)​

​

 

[Luke M]

Before going to using code, might I suggest you switch to using a Table? They were introduced in XL 2007, and are a powerful feature. One of the nice things is that it will automatically grow as you add data. See attached.

That said, code to put formula in visible cells would be something like this:

Sub AddFormula()
Dim lastRow As Long
lastRow = Range("A2").End(xlDown).Row
 
Range("C2:C" & lastRow).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeBlanks).Formula = _
    "=IF(OR(B2=""sent late"",B2=""not sent and late""),""Delay"","""")"
End Sub

 

[Akash kothari]

See this code based upon your sample file :​

    With ActiveSheet.AutoFilter.Range.Columns(3)
        .SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeBlanks).Formula = .Cells(2).Formula
    End With

Easy to adapt ! (column should be 8 …)
​

Thanks Marc,

That work well, Apologies for delayed response.

Akash

 

[Akash kothari]

Before going to using code, might I suggest you switch to using a Table? They were introduced in XL 2007, and are a powerful feature. One of the nice things is that it will automatically grow as you add data. See attached.

That said, code to put formula in visible cells would be something like this:

Sub AddFormula()
Dim lastRow As Long
lastRow = Range("A2").End(xlDown).Row
 
Range("C2:C" & lastRow).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeBlanks).Formula = _
    "=IF(OR(B2=""sent late"",B2=""not sent and late""),""Delay"","""")"
End Sub

Thanks Luke For valuable suggestion That works well.

Apologies for delayed response.

Akash