Selecting individual cells

[jstuexcel]

Hello everyone!

I'm hoping there's an easy way to select individual cells besides ctrl + click.

Basically, I need to select individual cells with the TRUE value to insert a row in between them.

Other than manually selecting the relevant cells, the closest I've gotten is using ctrl + F to find all the cells I want to select (in this case, cells with TRUE) and selecting all the results.

The issue with this is that if there are two TRUE results one after another, the selection includes both, rather than each individual cell.

To show you what I mean, here is what I'm getting:

But here is what I want:

Looking forward to hearing everyone's suggestions

 

[6StringJazzer]

If you do a Find All for TRUE and select all the results, and there is at least one TRUE in a different column, the TRUE matches in your desired column will be selected individually. The only way this would be useful to you is if you put a TRUE value in some other column way below your data so it will not affect your data when you insert rows.

The other solution would be to use VBA to insert the rows. We can do that if my suggestion above doesn't work for you.

 

[jstuexcel]

If you do a Find All for TRUE and select all the results, and there is at least one TRUE in a different column, the TRUE matches in your desired column will be selected individually. The only way this would be useful to you is if you put a TRUE value in some other column way below your data so it will not affect your data when you insert rows.

View attachment 115596

The other solution would be to use VBA to insert the rows. We can do that if my suggestion above doesn't work for you.

Thank you for your reply
For some odd reason, when I try your first suggestion in my main spreadsheet it doesn't work, but in a fresh sheet it does... I've removed conditional formatting and converted from a table to range to see if that's the issue but no luck.

When you get the chance I would love to see your VBA solution.

 

[jolivanes]

Change references as required.

Sub Maybe()
Dim rngVal As Range
Dim i As Long
Set rngVal = Nothing
Application.ScreenUpdating = False
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If Cells(i, 1).Value = "True" And Cells(i - 1, 1).Value = "True" Then
            If rngVal Is Nothing Then
                Set rngVal = Cells(i, 1)
                    Else
                Set rngVal = Union(rngVal, Cells(i, 1))
            End If
        End If
    Next
    rngVal.Select
    Selection.Insert Shift:=xlDown
Cells(1).Select
Application.ScreenUpdating = True
End Sub

 

[jstuexcel]

Change references as required.

Sub Maybe()
Dim rngVal As Range
Dim i As Long
Set rngVal = Nothing
Application.ScreenUpdating = False
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If Cells(i, 1).Value = "True" And Cells(i - 1, 1).Value = "True" Then
            If rngVal Is Nothing Then
                Set rngVal = Cells(i, 1)
                    Else
                Set rngVal = Union(rngVal, Cells(i, 1))
            End If
        End If
    Next
    rngVal.Select
    Selection.Insert Shift:=xlDown
Cells(1).Select
Application.ScreenUpdating = True
End Sub

Sorry I'm a VBA noob - are you able to tell me how to set a column other than A?

 

[jolivanes]

Change the "A" to "L" or "M" or "Z" or whichever Column you want.
You can also change to the Column numbers, A = 1, B = 2, C = 3 etc.

Sub Maybe()
Dim rngVal As Range
Dim i As Long
Set rngVal = Nothing
Application.ScreenUpdating = False
    For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        If Cells(i, "A").Value = "True" And Cells(i - 1, "A").Value = "True" Then
            If rngVal Is Nothing Then
                Set rngVal = Cells(i, "A")
                    Else
                Set rngVal = Union(rngVal, Cells(i, "A"))
            End If
        End If
    Next
    rngVal.Select
    Selection.Insert Shift:=xlDown
Cells(1).Select
Application.ScreenUpdating = True
End Sub

 

[footoo]

Here's an alternative macro. It is assumed that the TRUE values are the result of formulas.

Sub Insert_Rows()
Dim rng As Range, area As Range, y&
On Error Resume Next
Set rng = Range([A1], Cells(Rows.Count, "A").End(3)) _
    .SpecialCells(xlCellTypeFormulas, 4) 'Change column as required
On Error GoTo 0
If rng Is Nothing Then
    MsgBox "There are no cells containing TRUE"
    Exit Sub
End If
Application.ScreenUpdating = False
For Each area In rng.Areas
    For y = area.Count To 1 Step -1
        area(y).EntireRow.Insert
    Next y
Next
Application.ScreenUpdating = True
End Sub

 

[footoo]

Revised (I missed out a step) :

Sub Insert_Rows()
Dim rng As Range, area As Range, y&
On Error Resume Next
Set rng = Range([A1], Cells(Rows.Count, "A").End(3)) _
    .SpecialCells(xlCellTypeFormulas, 4) 'Change column as required
On Error GoTo 0
If rng Is Nothing Then
    MsgBox "There are no cells containing TRUE"
    Exit Sub
End If
Application.ScreenUpdating = False
For Each area In rng.Areas
    For y = area.Count To 1 Step -1
        If area(y) = True Then area(y).EntireRow.Insert
    Next y
Next
Application.ScreenUpdating = True
End Sub

 

[jstuexcel]

Revised (I missed out a step) :

Sub Insert_Rows()
Dim rng As Range, area As Range, y&
On Error Resume Next
Set rng = Range([A1], Cells(Rows.Count, "A").End(3)) _
    .SpecialCells(xlCellTypeFormulas, 4) 'Change column as required
On Error GoTo 0
If rng Is Nothing Then
    MsgBox "There are no cells containing TRUE"
    Exit Sub
End If
Application.ScreenUpdating = False
For Each area In rng.Areas
    For y = area.Count To 1 Step -1
        If area(y) = True Then area(y).EntireRow.Insert
    Next y
Next
Application.ScreenUpdating = True
End Sub

This worked perfectly! Thank you!

 

[jstuexcel]

Change the "A" to "L" or "M" or "Z" or whichever Column you want.
You can also change to the Column numbers, A = 1, B = 2, C = 3 etc.

Sub Maybe()
Dim rngVal As Range
Dim i As Long
Set rngVal = Nothing
Application.ScreenUpdating = False
    For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        If Cells(i, "A").Value = "True" And Cells(i - 1, "A").Value = "True" Then
            If rngVal Is Nothing Then
                Set rngVal = Cells(i, "A")
                    Else
                Set rngVal = Union(rngVal, Cells(i, "A"))
            End If
        End If
    Next
    rngVal.Select
    Selection.Insert Shift:=xlDown
Cells(1).Select
Application.ScreenUpdating = True
End Sub

Thank you