Selection.end in Excel

[GFAlexander]

I am creating a macro for an Excel spreadsheet and need to insert some totals at the bottom of a column. I have tried using "Selection.End(xlDown).Select" which takes me to the last cell in the column, but I then need to move down 2 more rows to give me a 1 row gap between the bottom of the range and where I want the sum to go.

The difficulty is that there will be a different amount of data everytime the macro is run and I always want to go to the bottom of the column + 2.

Can anyone help with some code?

Thanx

Gary

 

[GlennUK]

Use ..

Selection.End(xlDown).Offset(2,0).Select

to select the cell 2 rows after the end.

You don't need to select cells to enter formulae or values into them, in fact doing Select slows down your code compared to not doing it. Use Range(whatever).FormulaR1C1 = "formula", or .Value, or whatever you're trying to do, as this makes changes directly without selecting cells.

Glenn.

 

[GFAlexander]

Glenn,

Thanx for your help on this one, much appreciated.

Maybe you can also help on a similar question I have?

Again in the same spreadsheet which is always the same format in respect of the number of columns and the data that appears in them, but has a variable number of rows each time it is produced. I have a requirement to perform a COUNTIF function in the cell 2 below the bottom of the column (as per my first question). If I do this by recording a macro (cause I'm no programmer), then what gets recorded is:
"COUNTIF(R[-242]C:R[-2]C,"">=V"&quot"

Now the second part of that is OK cause the bottom cell of the column will always be -2 (relative to where I want the COUNTIF), but the first cell of the column will not always be -242. The first cell to check will in fact always be the 3rd cell/row in the column.

Do you have any ideas to help me?

Thanx again Glenn

Gary

 

[GlennUK]

Hi Gary, try something like this ...

"=COUNTIF(R3C:R[-2]C,"">=V"&quot"

which uses an absolute reference for the starting point ( R3C will always point at row 3 ).

Cheers, Glenn.

 

[GFAlexander]

Once again Glenn, Thanx very much.

Gary