How do I Select All Cells in row WHEN

[bmoore45]

Hey guys, I'm wondering if this is possible and how I can do it:

If cell A1 = "when" or "//" select row

So at the end I'll have all rows that begin with when or // selected.

Thanks!
Ben

 

[Yahya]

try this

Sub SelectCells()
Dim LR As Long, c As Range
Application.ScreenUpdating = False
LR = Range("a" & Rows.Count).End(xlUp).Row
For Each c In Range("A1:A" & LR)
    If c.Value <> "When" And c.Value <> "//" Then
        c.EntireRow.Hidden = True
    End If
Next c
With Range("A1:A" & LR)
    .SpecialCells(xlCellTypeVisible).EntireRow.Select
    .RowHeight = 15
End With
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Are you trying to do this manually or by macro?

Is it really from A1 down or do you have a header row in row 1?

 

[markmzz]

Another code:

Option Explicit
Sub mySelection()
    Dim LastRow As Long
    Application.ScreenUpdating = False
 
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Columns("B:B").Insert
    Range(Cells(1, 2), Cells(LastRow, 2)).FormulaR1C1 = _
        "=IF((RC[-1]=""when"")+(RC[-1]=""//""),1,"""")"
    Range(Cells(1, 2), Cells(LastRow, 2)). _
        SpecialCells(xlCellTypeFormulas, 1).EntireRow.Select
    Columns("B:B").Delete
 
    Application.ScreenUpdating = True
End Sub

Markmzz

 

[bmoore45]

Thanks guys, I tried the first codeblock and it didnt' work but the second worked fine for me, cheers

 

[Peter_SSs]

I tried the first codeblock and it didnt' work ..

That is quite likely because that code is case-sensitive (When/when)

I didn't get a response to my questions but it seems like a macro approach is required. I was considering an AutoFilter approach which is why I asked about a header row. For what it's worth, this was my idea if no header row. If there is a header row then the three lines marked with '**** could be omitted.

Further, if it is certain that there will always be at least one row of "when" or "//" then the lines marked with '##### could also be omitted.

If it is possible that no "when" or "//" rows exist, then the other codes will error.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Select_Rows()<br>    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Rows(1).Insert                      <SPAN style="color:#007F00">'*****</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("A1", Range("A" & Rows.Count).End(xlUp))<br>        .Cells(1, 1).Value = "tmp"      <SPAN style="color:#007F00">'*****</SPAN><br>        .AutoFilter field:=1, Criteria1:="=when", Operator:=xlOr, Criteria2:="=//"<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>    <SPAN style="color:#007F00">'#####</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> r = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0         <SPAN style="color:#007F00">'#####</SPAN><br>        .AutoFilter<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Rows(1).Delete                      <SPAN style="color:#007F00">'*****</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> r <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>    <SPAN style="color:#007F00">'#####</SPAN><br>        r.EntireRow.Select<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>                      <SPAN style="color:#007F00">'#####</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[bmoore45]

I see! Sorry for missing your question.

I'm actually interfacing with the worksheet through an AHK program that I am writing, so the macro approach is my preferred method as it is easier to simply run a macro via hotkey than to try and copy the process of manually inputting data.

 

[markmzz]

Thanks guys, I tried the first codeblock and it didnt' work but the second worked fine for me, cheers

Bmoore45,

Thanks for the feedback.

And for the note of Peter_SSs, here is the adjusted code:

Option Explicit
Sub mySelection()
    Dim LastRow As Long
    Application.ScreenUpdating = False
 
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Columns("B:B").Insert
    Range(Cells(1, 2), Cells(LastRow, 2)).FormulaR1C1 = _
        "=IF((RC[-1]=""when"")+(RC[-1]=""//""),1,"""")"
    [COLOR=blue]On Error Resume Next[/COLOR]
    Range(Cells(1, 2), Cells(LastRow, 2)). _
        SpecialCells(xlCellTypeFormulas, 1).EntireRow.Select
    [COLOR=blue]On Error GoTo 0[/COLOR]
    Columns("B:B").Delete
 
    Application.ScreenUpdating = True
End Sub

Markmzz