Selecting a block of cells using offset

[Mr2017]

Hi

I know how to select a block of cells using VBA.

But for this task, I have to be able to do it using the offset function, because data is inserted using the offset function.

So my question is this: in the code below, assuming that C3 (or C4, or C5 (depending on where the offset function ended up) is the last cell that the code went to, does anyone know how to highlight the three cells to the left, including C3 (or C4, or C5).

So if C3 was the last cell, C3, B3, and A3 would be highlithed and copied.

The code in my line below copies C3, B3, and A3, but I'd like to do it using the active cell and offset functions.

Can someone please advise?

Sub HighlightCells()


'ActiveCell.Offset(0, -2).Select


Range("C3", Range("C3").End(xlToLeft)).Copy


End Sub

 

[Fluff]

Is this what you mean?

    ActiveCell.Offset(, -4).Resize(, 3).Copy

 

[Mr2017]

Hi Fluff

Thanks for the prompt response.

That code works if the active cell is E3, F3 or a column further down the alphabet.

But it doesn't work if the active cell is C3 (column C will always be the last column where data is inserted).

But I changed the 4 to a 2 and it now works, as intended.

Thank you for your guidance on this, it's useful!

Forgot to ask how I can then paste the data from the three cells that have just been copied, as values?

Selecting a cell using Range then pasting data into it is easy.

But in this case I'd just like to copy then paste the values in those cells as text rather than formulae.

Any thoughts, please?

 

[Fluff]

Where do you want to paste the values?

 

[Mr2017]

In the cells that have just been copied.

So if cells C3, B3 and A3 have just been copied, then I'd paste values in those cells.

If cells C4, B4, and A4 were just copied, then I'd paste those cells as values (as I need to get rid of the formulae in the cells).

The intention is to keep previous records as values, but have the latest row as formulae.

Does that make sense? If not, please let me know if you'd like me to clarify further.

TIA.

 

[Fluff]

How about

    With Cells(ActiveCell.Row, 1).Resize(, 3)
        .Value = .Value
    End With

This will convert cols A:C to values, regardless of which column the activecell is.

 

[Mr2017]

That's great!

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback