Iterate and select a specific column

[pessanha.matheus (Matheus)]

How can I select the first column from AJ onwards whose rows from 14 to 115 have all ‘‘To be checked’’ as content?

[Shashi123 (Shashi)]

Hi,
You can use read range activity and get the entire data in datatable. and again use filter datatable activity.

[pessanha.matheus (Matheus)]

Hello Shashi123, I don’t think this is the right solution. I just want to select a column that met a certain condition. The condition is: all its rows should contain ‘‘To be checked’’.

[Lakshay_Verma]

Hi @pessanha.matheus

You need to provide more detail on selection of data.
If your column position is fixed i.e. AJ in your case, what is the criteria for Row selection.
If row selection is based on “To be Checked” Keyword.

If above two cases are correct, you can do follwoing.

1. Open excel application scope.
2. rangeString=Lookup Range =“To be checked”
3. rowNumber=Extract only numbers from rangeString(apply regex here i.e. \d).
4. Read range this data with range as AJ{{rowNumber}} without headers.

Hope this helps

[pessanha.matheus (Matheus)]

I solved this problem with a VBA code:

Option Explicit

Sub SelectCol()
Application.ScreenUpdating = False

Dim W As Worksheet
Dim Verif As Boolean
Dim NextCol As Integer
Dim ColSelect As Integer
Dim DernLin As Long

Set W = Sheets(“PCC Checklist”)
DernLin = W.Rows.Count

W.Select

W.Range(“AJ10”).UnMerge

W.Cells(14, 36).Select

Do While ActiveCell.Column < 85 'Last PCC Column
Do While ActiveCell.Value <> “”
If ActiveCell.Value <> “To be ckecked” Then
Exit Do
ElseIf ActiveCell.Row < 115 Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.EntireColumn.Select
Exit Sub
End If
Loop
NextCol = ActiveCell.Column + 1
W.Cells(14, NextCol).Select
Loop

Application.ScreenUpdating = True
End Sub