CODEWITHSUNDEEP
excelvba
Strelok
Strelok

Reputation: 189

Select cells in Microsoft Excel based on the values of a given column

First of all, I'm a complete novice in VBA, and my following considerations are based on my search on the subject

Here's my problem: I need to remove, for each line, the amount of cells indicated in purple. I'm trying to write on a small VBA script that allows me to automate this. Since this range is contiguous (I have to remove all cells starting from column F to the right), I am aware that using VBA, I can use Range().Select

Ideally, I've thought the workflow would be something like this:

    for each line in {2,N}
    do 
    fcol = 6 + "$EN" # "$EN" means the value of the cell located in fixed column E, N line; fcol is the final column number
    Range(6,N:fcol,N).Select # from six column (F) to end column, "fcol"
enddo

but I am clueless how to implement it in VBA :| Any help would be most welcome!

enter image description here

Upvotes: 0

Views: 266

Answers (2)

Evil Blue Monkey
Evil Blue Monkey

Reputation: 2609

If i got it right, you want to delete the cells to the right of the column E according to the value in the column E (each row its own value). In such case i've written down this:

Sub SubDeleteCells()

    'Declarations.
    Dim RngCommanderColumn As Range
    Dim RngTargetCell As Range

    'Setting variable.
    Set RngCommanderColumn = Range(Range("E2"), Range("E" & Rows.Count))

    'Covering each cell in RngCommanderColumn.
    For Each RngTargetCell In RngCommanderColumn
        'Checking if RngTargetCell is blank; in such case i leave the For-Next cycle.
        If RngTargetCell.Value = "" Then GoTo TargetCellBlank

        'Checking if the value in RngTargetCell is numeric.
        If IsNumeric(RngTargetCell.Value) Then
            'Checking if the value in RngTargetCell greater than 0.
            If RngTargetCell.Value > 0 Then
                'Deleting the cells.
                Range(RngTargetCell.Offset(0, 1), RngTargetCell.Offset(0, RngTargetCell.Value)).Delete xlShiftToLeft
            End If
        Else
            'Reporting the invalid value.
            MsgBox "Cell " & RngTargetCell.Address & " contains an invalid value", , "Invalid input: " & RngTargetCell.Value
        End If

    Next

TargetCellBlank:

    'Reporting.
    MsgBox "Cells from " & RngCommanderColumn.Cells(1, 1).Address & " to " & RngTargetCell.Address & " evaluated.", , "Task completed"

End Sub

Fell free to ask about any line and welcome to VBA. It's amazing.

Upvotes: 1

HTH
HTH

Reputation: 2031

try this

Sub test()
    Dim cel As Range

    For Each cel In Range("E2", Cells(Rows.Count, 5).End(xlUp))
        If cel.Value > 0 Then cel.Offset(, 1).Resize(, cel.Value).ClearContents ' this will delete cell content only
        If cel.Value > 0 Then cel.Offset(, 1).Resize(, cel.Value).Delete xlToLeft ' this will delete cells and shifting left those at the right of deleted ones
    Next
End Sub

just remove the line you don't need based on the comments I left

Upvotes: 1

Related Questions