How is it possible to determine the offset number---from an active cell---to the next column that contains data to the left of the active cell

[dougmarkham]

Hi Folks,

Couldn't find anything on Google for this, so thought I'd ask.

If my active cell was in D4 and the next cell to the left of D4 with a data value in was B4:
Is it possible via VBA to determine the offset number from the Active cell (in this case -2)?

My goal is to carry out a recursive operation in a particular column to cut and paste the contents of that column to the furthest blank cell to the left, hence the need to know the offset.

In general, is it possible via VBA code to measure the number of cells from an active cell (both left and right) before reaching a cell with data in it?

Kind regards,

Doug

 

[mikerickson]

With ActiveCell
    With Range(.EntireRow.Cells(1,1), .Offset(0, -1))
        With .SpecialCells(xlCelltypeConstants)
            With .Areas(.Areas.Count)
                msgBox .Cells(1, .Cells.Count) & "has data"
            End With
        End With
    End With
End With

 

[dougmarkham]

With ActiveCell
    With Range(.EntireRow.Cells(1,1), .Offset(0, -1))
        With .SpecialCells(xlCelltypeConstants)
            With .Areas(.Areas.Count)
                msgBox .Cells(1, .Cells.Count) & "has data"
            End With
        End With
    End With
End With

Hi Mike,

Thanks your replying!

I ran the code; however, this code returns the cell's value e.g., it returns "Unit 1" from below, rather than the number of cells to the left of the ActiveCell (D6), i.e., -2.

Is there a way to return the offset number?

Kind regards,

Doug.

 

[mikerickson]

I forgot to use .Address proerty in my demo code (got the default .Value)

If you get the .Column property for that cell and subtract from ActiveCell.Column, you should get the offset you seek.

 

[dougmarkham]

I forgot to use .Address proerty in my demo code (got the default .Value)

If you get the .Column property for that cell and subtract from ActiveCell.Column, you should get the offset you seek.

Hi Mike,

Didn't know what most of the code did, so I recorded a bunch of macros [to test .SpecialCells(xlCellTypeConstants)] and also tested the individual lines of your code. Very instructive.
Thanks for your help, I've modified the code and got the below to work as required:

Sub Test9()

Dim LCol As Integer
Dim RCol As Integer

With ActiveCell
    With Range(.EntireRow.Cells(1, 1), .Offset(0, -1))
        With .SpecialCells(xlCellTypeConstants)
            With .Areas(.Areas.Count)
                LCol = .Cells(1, .Cells.Count).Column
            End With
        End With
    End With
End With
RCol = ActiveCell.Column
MsgBox (1 + LCol) - RCol

End Sub

I'm now getting this.

Now I will create an object for this offset value (x) and use this as the Column number in a recursive cut/paste operation from the ActiveCell: going down every cell in a column i.e., so that I can move the data---e.g., in an 'Address line 3' across to 'Address line 2'---if the customer wrongly left 'Address line 2' blank when they were filling-in their address details.

Thanks for your help!

Kind regards,

Doug.

 

[dougmarkham]

Just for interests if anyone is looking for something similar, here is the code I've got to move cell contents left should blanks exist (to the farthest blank to the left)

Sub ShiftLeftToRemoveSpaces()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual

Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnData As Range
Dim rnCell As Range
Dim rnSelect As Range

Dim LCol As Integer
Dim RCol As Integer

Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("AddressData")

With wsSheet
Set rnData = .Range("AddrTable[Address line 2]")
'MsgBox rnData.Address
End With

For Each rnCell In rnData
    If rnCell.Offset(0, -1) <> "" Then
        GoTo BB
    Else
        rnCell.Select
        With ActiveCell
            With Range(.EntireRow.Cells(1, 1), .Offset(0, -1))
                With .SpecialCells(xlCellTypeConstants)
                    With .Areas(.Areas.Count)
                        LCol = .Cells(1, .Cells.Count).Column
                    End With
                End With
            End With
        End With
        RCol = ActiveCell.Column
        x = (1 + LCol) - RCol
        MsgBox x
        ActiveCell.Offset(0, x).Value = ActiveCell.Value
        rnCell.ClearContents
BB:
    End If
Next rnCell

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic

End Sub