Selecting Fixed cell to final value

[adruss85]

Hi, a relative newbie to macro writing so please go easy on me!

I have a macro in which the number of populated columns will vary. I'm trying to select from B1 to the last occupied cell in a row 1, copy, then paste the data into another location.

So far i have selected the final cell using

Range("IV1").End(xlToLeft).Select

But then i run into problems selecting from B1 to this cell. Logic told me that

Range("B1:ActiveCell").Select

Would work but guess what, it didnt!

Also, i'd like to delete the 3 columns to the right of the final value, but as of yet have no idea of the code for when the final column changes.

Thanks for any help you can give!

 

[VoG]

Hello and welcome to MrExcel.

Try something like

Dim LC As Integer
LC = Cells(1, Columns.Count).End(xlToLeft).Column
Range("B1:B" & LC).Copy Destination:=Sheets("Sheet1").Range("A1")

 

[adruss85]

Hi, thanks for the welcome.

I changed the code to

Dim LC As Integer
        LC = Cells(1, Columns.Count).End(xlToLeft).Column
        Range("B1:B" & LC).Copy Destination:=Sheets("ActiveSheet").Range("V1")

but now i get a error of Type mismatch.

I also i tried

    Dim LC As Integer
        LC = Cells(1, Columns.Count).End(xlToLeft).Column
        Range("B1:B" & LC).Select
        Selection.Copy
        Range("V1").Select
        ActiveSheet.Paste

But noticed that this selected down although pasted into the correct location.

If i were to write hard code it would look sometthing like this

    Range("B1:P1").Select
    Selection.Copy
    Range("V1").Select
    ActiveSheet.Paste

However what i'd like to do is replace P1 with the last cell that contains data, as i have a varying number of columns.

If i can get this bit sorted i'll have a go at column removal myself!

 

[VoG]

If you are pasting to the same sheet you don't need to specify the sheet:

Dim LC As Integer
LC = Cells(1, Columns.Count).End(xlToLeft).Column
Range("B1:B" & LC).Copy Destination:=Range("V1")

 

[adruss85]

That code seems to be counting the number of columns, LC, but then selecting from B,1 down to B,[1+LC], then pasting into V1.

Actually it needs to count LC then select B,1 to [B+LC(as a letter)],1

Do i just need to change a "Column" into a "Row"?

Thanks

 

[VoG]

Silly me

Range(Cells(1, 2), Cells(1, LC)).Copy Destination:=Range("V1")

 

[adruss85]

Great, thanks!

No doubt i'll be asking more questions soon enough!

 

[adruss85]

Wow, it's amazing what one piece of code can allow you to learn/do!

I do have one final question however:

This code:

Range(Cells(1, 2), Cells(1, LC)).Copy Destination:=Range("S1")

Allows the macro to count then select the number of cells, copy them and paste them across from S1. The problem i face is that this is hard coded, and the number of records i could have in the columns can vary.

I worked out that if LC is the number of records from B1 across, then i need to paste into the Cell (1, LC + 3).

I tried the code:

Range(Cells(1, 2), Cells(1, LC)).Copy Destination:=Range(Cells(1, LC + 3))

but it didn't work (runtime error). Have i made some hideous newbie mistake?

Thanks once again!

 

[VoG]

Try

Range(Cells(1, 2), Cells(1, LC)).Copy Destination:=Cells(1, LC + 3)

 

[adruss85]

That works perfectly, brilliant thanks!