Search code examples
excelvbafunctioncall

Delete an Excel Column while inside another subroutine?


I have a Macro that concatenates the values in two adjacent columns. It works perfectly. I'd like to add a command to then delete the second column once the concatenation is complete. Can I do this from inside the existing subrouting/macro or do I need to call a separate function? Here is what I have:

Option Explicit
Sub Doc1_Doc2_Merge()

Dim CurrCol As Integer
Dim NewValue As String

CurrCol = 1

While Cells(1, CurrCol).Address <> "$JL$1"

    'MsgBox Cells(1, CurrCol).Address & " " & Cells(1, CurrCol).Value
    If InStr(Cells(1, CurrCol).Value, "Doc1") > 0 Then
        ' look at next cell
        If InStr(Cells(1, CurrCol + 1).Value, "Doc2") > 0 Then
            If Trim(Cells(2, CurrCol + 1).Value) <> "" Then
                NewValue = Cells(2, CurrCol).Value & ", " & Cells(2, CurrCol + 1)
                'MsgBox "New Value is " & NewValue
                Cells(2, CurrCol).Value = NewValue
            End If
        End If

    End If
    'now delte currCol+1

    'This is the deletion part that isn't working
    Column(CurrCol + 1).Select
    Selection.Delete Shift:=xlToLeft

    'Advance the counter
    CurrCol = CurrCol + 1
Wend

End Sub

Solution

  • Replace

    Column(CurrCol + 1).Select
    

    with

    Range(Columns(CurrCol + 1), Columns(CurrCol + 1)).Select