98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Unlock cells in a Range based on condition | Excel forum
Unlock cells in a Range based on condition | Excel forum
Resolved · High Priority · Version 2016
Sat 11 Sep 2021, 17:49Delegate Ajay said...
Ajay has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Unlock cells in a Range based on condition
Hi Jen,
I am using the code provided by you earlier.
Dim cl As Range
For Each cl In ActiveSheet.UsedRange
If cl.Interior.Color = RGB(100, 100, 100) Then
cl.Locked = False
End If
Next cl
End Sub
The last row in my range is Row 43, however the used range row is 6397
the Range in each of my workbook is different.
How do I change the property Used range to the last cell in the range?
Can I attach a screen snip to explain the problem better.
I have tried using the code to reset the usedrange, but it does not work
Reset UsedRange
Sub UsedRange()
a = ActiveSheet.UsedRange.Rows.Count
End Sub
The value of variable a is still 6397
Can you kindly assist in resetting the used range or provide an alternative solution to the problem?
Regards
Ajay
Wed 15 Sep 2021, 09:14Trainer Jens said...
RE: Unlock cells in a Range based on condition
Hi Ajay,
Thank you for the forum question.
Please select all rows under your data. If row 44 is the first blank row then click on 44 (the row number left on the sheet) to select the whole row. Press Ctrl shift [down arrow] to select all rows down. Clear All from the Editing group on the Home tab. It is not enough to delete you have to clear all.
You must have something you haven't cleared.
You can always check where your usedrange end by selecting A1 on a sheet and press Ctrl END.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 15 Sep 2021, 09:14Trainer Jens said...
RE: Unlock cells in a Range based on condition
Hi Ajay,
Thank you for the forum question.
Please select all rows under your data. If row 44 is the first blank row then click on 44 (the row number left on the sheet) to select the whole row. Press Ctrl shift [down arrow] to select all rows down. Clear All from the Editing group on the Home tab. It is not enough to delete you have to clear all.
You must have something you haven't cleared.
You can always check where your usedrange end by selecting A1 on a sheet and press Ctrl END.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 16 Sep 2021, 17:59Delegate Ajay said...
RE: Unlock cells in a Range based on condition
Hi Jens,
You are a star.
The solution has worked and now the usedrange is now 1 row greater than the last row.
I am not sure whether the last row and the usedrange.row can be the same, else I will use the offset function
Many many thanks
Ajay
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Viewing Many WorksheetsIf there are more worksheets in your workbook than there is room to show all their tabs at the bottom of the screen - Right click on the navigation arrows. A list of all your worksheets is shown. You just click on the one that you want to access. If you have more than 15 worksheets, select in the list and choose your worksheet from hundreds. |
