CODEWITHSUNDEEP
vbaexcel
ChenLee
ChenLee

Reputation: 1

How can I use a range in Excel VBA with shifting columns?

I am working in Excel VBA. I am running a regression. The data in column 'O' is the dependent variable. This regression is being run 8 times using a do while loop.

The problem that I am having is as follows. On each pass through, I am deleting a column. This of course moves column O to column N on the first pass, column M on the second pass, etc.

I need to find a way using a range to do this WITHOUT using an if then statement. Can anyone shed light on this?

Upvotes: 0

Views: 1407

Answers (4)

user2140261
user2140261

Reputation: 7993

IS the data in a table? Or at least have a header? You could use match if there is a header but no table, =MATCH("Column Header",1:1,0) will return the number for the column that contains that header.

enter image description here

Otherwise if you have headers and a table you could simply call it with TableName[HeaderName] will pull all data from the column in the table under the header you specify. Notice below how all data below the specified Header is highlighted.

enter image description here

Either way the data will always remain constant to your information as long as the header remains in the row you specify OR the table.

Upvotes: 0

SeanC
SeanC

Reputation: 15923

named ranges will move with the selection. you then use this name in your code. e.g. MyData is defined as Sheet1!O:O. If a column is deleted, then Excel will alter the address of the named range automatically.

Example:

ActiveWorkbook.Names.Add Name:="TempRange", RefersTo:="=Sheet1!O1"
Range("TempRange").Value = Range("TempRange").Address
Range("A:A").Delete
Range("TempRange").Value = Range("TempRange").Address

also, note that the address doesn't change, BUT the cell it references does, as you will see the value $O$1 in both O1 and N1

Upvotes: 1

Shari W
Shari W

Reputation: 537

Hard to imagine exactly what you are doing without seeing the code, but you might consider using the Range Offset function. See how the value of your range changes as you delete columns before, after and on this range. Then design a small statement that can handle these situations.

For instance:

Option Explicit

Sub x()

    Dim rngCurrent As Range
    Dim wksCurrent As Worksheet

    Set wksCurrent = ActiveSheet

    ' Example 1: The range in question is in a column after the column to delete

    Set rngCurrent = wksCurrent.Cells(20, 31)

    Debug.Print rngCurrent.Address '$AE$20

    wksCurrent.Range("O:O").Delete

    Debug.Print rngCurrent.Address '$AD$20  (it decreased automatically)

    Set rngCurrent = rngCurrent.Offset(0, -1) ' Reset column to previous column, in this case 30

    Debug.Print rngCurrent.Address '$AC$20


    ' Example 2: The range in question is a column before the deleted column
    Set rngCurrent = wksCurrent.Cells(20, 3)

    Debug.Print rngCurrent.Address '$C$20

    wksCurrent.Range("O:O").Delete

    Debug.Print rngCurrent.Address '$C20  (no change since the deleted column was after this range)

    Set rngCurrent = rngCurrent.Offset(0, -1) ' Reset column to previous column, in this case 30

    Debug.Print rngCurrent.Address '$B20


    ' Example 3: The range in question is the same as the deleted column
    Set rngCurrent = wksCurrent.Cells(20, 15)

    Debug.Print rngCurrent.Address '$O$20

    wksCurrent.Range("O:O").Delete

    'Debug.Print rngCurrent.Address 'Error: Object Required, the cell pointed no longer exists

    'Set rngCurrent = rngCurrent.Offset(0, -1) ' Error: Object Required

    'Debug.Print rngCurrent.Address '$O19  ' Error: Object Required

    ' Example 4: The range in question is the same as the deleted column. Avoid the error.
    Set rngCurrent = wksCurrent.Cells(20, 15)

    Debug.Print rngCurrent.Address '$O$20

    wksCurrent.Range("O:O").Delete

    Set rngCurrent = wksCurrent.Range("O20").Offset(0, -1) ' Refer to the worksheet to reset the range, instead of to the range itself

    Debug.Print rngCurrent.Address '$N20

End Sub

Upvotes: 0

Teeracroptus
Teeracroptus

Reputation: 120

You can access the cells as:

.Cells(RowNumber,ColumnNumber)

Everytime you delete a column, you can decrement the column number by one to achieve your objective.

Upvotes: 1

Related Questions