CODEWITHSUNDEEP
excelvbaselect
Piotr
Piotr

Reputation: 13

Select all unlocked cells under ActiveCell in same column

I'm trying to create VBA code which will let me select all unlocked cells in particular Column under ActiveCell.

Trying to resolve it myself, I came across code which partly does the thing: Select all blanks cells in a column

An also on other page I found code for selecting unlocked cells in worksheet: https://www.extendoffice.com/documents/excel/1279-excel-select-all-unlocked-cells.html

This code selects all rows which are blank, but in fixed column.

Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:A" & lr).SpecialCells(xlCellTypeBlanks).Select

While this will result in choosing all unlocked cells in worksheet:

Dim WorkRng As Range
Dim OutRng As Range
Dim Rng As Range
On Error Resume Next
Set WorkRng = Application.ActiveSheet.UsedRange
Application.ScreenUpdating = False
For Each Rng In WorkRng
    If Rng.Locked = False Then
        If OutRng.Count = 0 Then
            Set OutRng = Rng
        Else
            Set OutRng = Union(OutRng, Rng)
        End If
    End If
Next
If OutRng.Count > 0 Then OutRng.Select
Application.ScreenUpdating = True
End Sub

What I expected to get is selecting all cells which are unlocked (they might be filled or not), in column manualy selected in excel.

Example: Range A1:A4 - is locked, A5 is unlocked, Range A6:A7 is locked, Range A8:A14 is unlocked, A15 is locked, A16:A22 is unlocked Same for columns B,C,D

Select A5 :Use MACRO, Result> Select A8:A14 & A16:A22, Select B5 :Use MACRO, Result> Select B8:B14 & B16:B22,

This fill let my either fill selected rows with same content or Delete values from them by one click.

I wasn't able to go pass this:

Worksheets("Sheet1").Select
ActiveCell.Offset(rowOffset:=3).Activate

Range(ActiveCell, ActiveCell.End(xlDown)).SpecialCells(xlCellTypeBlanks).Select

But this gives me only: B8:B14 and for range B16:B22 I need to create new MACRO which in the end does not met my requirements.

Upvotes: 1

Views: 127

Answers (1)

Jochen
Jochen

Reputation: 1254

If the code for selecting the unlocked cells is running (and fast enough for your needs), you only have to adjust your WorkRngto the desired "column below (including?) your active cell. Please see the following code:

Sub Test()
    Dim WorkRng As Range
    Dim OutRng As Range
    Dim Rng As Range
    'On Error Resume Next
    'Set WorkRng = Application.ActiveSheet.UsedRange
    Set WorkRng = Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column))
    Application.ScreenUpdating = False
    For Each Rng In WorkRng
        If Rng.Locked = False Then
            'If OutRng.Count = 0 Then
            If OutRng Is Nothing Then
                Set OutRng = Rng
            Else
                Set OutRng = Union(OutRng, Rng)
            End If
        End If
    Next
    If OutRng.Count > 0 Then OutRng.Select
    Application.ScreenUpdating = True

End Sub

Upvotes: 0

Related Questions