Last Row

[Vincent Thank You]

Hello,

The following code is not allowing me to integrate the "lastrow" formula and is creating 0's well past the data range.

On Error Resume Next
With ActiveSheet.Range("N2:N2" & Cells(Rows.Count, 2).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
.Cells.FormulaR1C1 = "=RC[-2]"
.Cells.FillDown

When I do integrate it the way I see fit the formula doesn't preform properly. Thanks!

 

[mrshl9898]

Try this:

[COLOR=#333333]On Error Resume Next[/COLOR]
[COLOR=#333333]With ActiveSheet.Range("N2:N" & Cells(Rows.Count, 2).End(xlUp).Row).SpecialCells(xlCellTypeVisible)[/COLOR]
[COLOR=#333333].Cells.FormulaR1C1 = "=RC[-2]"[/COLOR]
[COLOR=#333333].Cells.FillDown[/COLOR]

 

[Vincent Thank You]

lol. That worked! Thank you very much! That simple little 2. What am I telling the macro to do differently by removing that 2?

 

[Dim Me as xlNoob]

Basically you were adding a leading 2 to the real last row. So if your last row was 20, then the code would run to row 220.

 

[mrshl9898]

With

[/COLOR][COLOR=#333333]Cells(Rows.Count, 2).End(xlUp).Row

You are counting rows, say it's 1000.

By having "N2:N" & 1000 you have "N2:N1000".

By having "N2:N2" & 1000 you have "N2:N21000".

 

[Vincent Thank You]

Ahhhhh, I see. Thank you for the information. I'm actually trying to drag the formula down to the bottom of the data range to visible cells only. So, with this formula, if it's only 500 rows, it's going to extend that by 500 with more 0's, correct? The

 

[mrshl9898]

I have never tries with visible only. Looks like you'll find an answer here though.

https://stackoverflow.com/questions...lling-down-a-formula-for-visible-cells-in-ano

 

[MARK858]

I'm actually trying to drag the formula down to the bottom of the data range to visible cells only

Why would you need to use Filldown as the code already puts the formula in the visible cells?

On Error Resume Next
ActiveSheet.Range("N2:N" & Cells(Rows.Count, 2).End(xlUp).Row).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=RC[-2]"
On Error Goto 0

 

[Vincent Thank You]

It seems to be working, I was just looking for further information as to why. The reason I was trying to fill down was because the data range is a variable, It's not always the same length. I was trying to fill down, visible cells only to the bottom of the data range.

 

[mrshl9898]

[COLOR=#333333]Cells(Rows.Count, 2).End(xlUp).Row[/COLOR]

deals with the variable part

it's counting the rows

basically it's the same as selecting the last cell column B and pressing ctrl + up