VBA to create a range of non contiguous cells based on condition and then use it as Range Unlocked by password when sheet is protected

excells

New Member
Joined
Feb 6, 2014
Messages
29
Hi all,
hope all is well.

Im trying to:
1. Protect a sheet using VBA. (I got this to work)
2. Use a range created using VBA as "Range Unlocked by password when sheet is protected" (I'm not sure if this will work, maybe someone can provide comments)
3. Use VBA to create a range of non contiguous cells based on condition NON-BLANK (this is where Im stuck at the moment).

The problem (item 3 above) is that the loop executes but:
a. Im never able to see if the cells required have actually been added to the range.
b. I cannot select the range to view it

Could somebody have a look and provide some ideas as to what I could be doing wrong? Below Im pasting the code and a link to the table below

Thanks in advance for your assistance

Code:
Sub Create_WorkRange()


    Dim WorkRange As Range
    Dim Lrow As Long
    Dim i As Long


    'Find the last used row:
    Lrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Sheets("Sales and cost of sales").Select
    
  'Loops checking for NON_BLANKS in column F:

    For i = 3 To Lrow
        If Range("F" & i) <> "" Then
        
            If WorkRange Is Nothing Then
                Set WorkRange = Range("F" & i & ":" & "O" & i)
   
            Else
                Set WorkRange = Union(WorkRange, Range("F" & i & ":" & "O" & i))
            End If
        
        End If
       
        
        
    Next i
    
    'With the following line I try to select the created range so I can view it
    'but it gives me error:
    'Run-time error '1004:
    'Methor 'Range' of object'_Global' failed
    
    'hence I commented it
    
    'Range("WorkRange").Select
    
    'The following line actually writes the range but it refers to "WorkRange"
    'and as result I dont know which cells it is actually adding to the range
    
    ActiveSheet.Names.Add Name:="WorkRangePermission", RefersTo:= _
        "WorkRange"
                
               
                
End Sub


http://s8.postimg.org/kpb1vwc1h/Picture_of_table.png
 
Last edited:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

excells

New Member
Joined
Feb 6, 2014
Messages
29
Hi Sericom,
Thanks so much for the prompt response. It worked perfectly.
cheers
 

Forum statistics

Threads
1,136,275
Messages
5,674,783
Members
419,524
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top