I Want some help in selecting ranges in vba

Question

Range("B2:C2").Select 
Selection.Copy 
Range("J2").Select 
Selection.End(xlDown).Select 
Range("B82706:C82706").Select 
Range("C82706").Activate 
Range(Selection, Selection.End(xlUp)).Select 
ActiveSheet.Paste 
Columns("B:C").Select 
Range("B82695").Activate 
Application.CutCopyMode = False 
Selection.Copy 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False

in this case , B82706 & c82706 row nos may vary so , instead of this , i have to select the B&C column based on last J column (till end data)

kindly help me with it , thanks in advance

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/a/284237/3219613) — Pᴇʜ, Feb 09 '21 at 07:38
It's a little difficult to follow the macro recorder code, so can you describe in words what you want to achieve? — Tim Williams, Feb 09 '21 at 07:45
i have entered some formula in B2 & c2 and i want that formula to copy paste in all the further B & C columns in this case last B & c column is B82706 & C82706 . instead of entering B82706 & C82706 , i want a code which helps me to auto select the B&C column with respect to End data in J column — Arun Kumar G., Feb 09 '21 at 07:49

Pᴇʜ · Accepted Answer · 2021-02-09T08:21:25.260

Without seeing your data I'm not really sure if this is correct, but it should look something like this:

Find the last used row in column J (this is the end of your paste).
Find the last used row in column B (this is the beginning of your paste).
Copy/paste
Convert the formulas into values

Option Explicit

Public Sub Example()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'define your worksheet name here
    
    Dim LastRowInJ As Long 'find last used row in column J
    LastRowInJ = ws.Cells(ws.Rows.Count, "J").End(xlUp).Row

    Dim LastRowInB As Long 'find last used row in column B
    LastRowInB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ws.Range("B2:C2").Copy Destination:=ws.Range("B" & LastRowInB, "C" & LastRowInJ)
    Application.CutCopyMode = False
    
    'turn formulas into values in column B and C
    ws.Columns("B:C").Value = ws.Columns("B:C").Value

End Sub

Always make sure your Range and Columns objects are referenced to a workbook. Otherwise Excel might guess the wrong workbook.

Also avoid using .Select as this slows down your code a lot and makes it less reliable as the selection can be changed easily by a mouse click: How to avoid using Select in Excel VBA.

score -1 · Answer 2 · answered Feb 09 '21 at 11:01

-1

I am not sure what the problem is you are trying to solve. If you make use of named ranges in Excel then you don't need to be concerned that the VBA will refer to incorrect ranges if user insert rows or columns into the Excel file.

answered Feb 09 '21 at 11:01

MrT

61
4

I Want some help in selecting ranges in vba

2 Answers2