0

So I lost all of my VBA notes and am having issues redoing some logic here so I apologize if this looks very basic. I am trying to find set the top and find the last populated cell in a column and set them both in my range to be able to copy and paste it into a log. When I am getting to the MYRANGE part, I am getting:

error 1004

I've tried using a more basic selection to achieve what I'm trying to do, but the raw data is never going to be the same size so I wanted to come back to this to try and see if I could get help.

Sub CopyRows()

    Dim sht As Worksheet
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim MYRANGE As Range
    Dim Top, Bottom, Left, Right As Long        

    Set sht = Worksheets("Data")
    sht.Activate

    Set Top = Range("B2")
    LastRow = Cells(Rows.Count, "B").End(xlUp).Select
    Set MYRANGE = Range("Top", "LastRow").Select.Copy

End Sub

As I mentioned, I am trying to set my range as the "Top" and "Bottom" or "LastRow" so that way I don't have to worry about the actual size of the data and copy and paste it over to the other worksheet.

Vityata
  • 42,633
  • 8
  • 55
  • 100
richard jahn
  • 17
  • 4
  • 2
    Side note: you want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code – cybernetic.nomad Jul 19 '19 at 15:55

2 Answers2

2

When setting a range you do not use .Select or .Copy

When using variable ranges in another range you do not use ""

And do not use .Activate or .Select

Sub CopyRows()
    Dim sht As Worksheet
    Dim LastRow As Range
    Dim LastColumn As Long
    Dim MYRANGE As Range
    Dim Top As Range, Bottom As Range, Left As Range, Right As Range

    Set sht = Worksheets("Data")

    Set Top = sht.Range("B2")
    Set LastRow = sht.Cells(Rows.Count, "B").End(xlUp)
    Set MYRANGE = sht.Range(Top, LastRow)
    'Now you can do something with MYRANGE like MYRANGE.Copy
End Sub
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
0

In the general case, a range in Excel is defined with either two cells or with one cell. One cell is needed for the definition of a single cell range and two cells are needed for multiple cell ranges.

In your case, you need a two cell range in Excel, thus you need to define the upper left cell and the lower right cell of the range:

The code below defines the upper left cell (startRange) and the lower right cell (endRange):

enter image description here

Then it defines the myRange, as all the cells which are in the tetrangular between these two:

enter image description here

Sub TestMe()

    Dim wks As Worksheet
    Dim lastRow As Long

    Dim startRange As Range
    Dim endRange As Range
    Dim myRange As Range

    Set wks = Worksheets("Data")
    lastRow = wks.Cells(wks.Rows.Count, "B").End(xlUp).Row

    Set startRange = wks.Range("B2")
    Set endRange = wks.Range("B" & lastRow)

    Set myRange = wks.Range(startRange, endRange)
    Debug.Print myRange.Address
    myRange.Offset(0, 1).Value = myRange.Value

End Sub

Additional points of the code:

  • it does not use .Select or .Activate (How to avoid using Select in Excel VBA)
  • it always refers to the parent worksheet of the Range. E.g., wks.Range("B2")
  • the code does not copy cells, but takes their values and writes them to the next column with myRange.Offset(0, 1).Value = myRange.Value, which may not be what is needed, if the style and the formats of the cell are important as well.
Vityata
  • 42,633
  • 8
  • 55
  • 100