Macro which will hide the part of the sheet for any particular s

Hi Sanjay,

Thank you for the forum question. You can only hide whole rows or whole columns in Excel, but a walk around it can be move the data to another destination and then clear them.

I know you are not interested in adding more worksheets to the workbook. In my code below I use a range starting from A4000. If you are not using this range for anything you can keep the code as it is. You will have to amend something.

In my code I had the dropdown list in B9. You need to change this to the cell you use.You will also have to amend the following lines to fit you data:
Sh.Range("f6:l13").Copy
Sh.Range("f6").Select
Sh.Range("f6:l13").Clear






Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = False
Set Sh = Sheets("Test")
If Not Application.Intersect(Target, Sh.Range("b9")) Is Nothing Then

If Range("B9").Value = "VGE" Then


Sh.Range("f6:l13").Copy
Sh.Range("a4000").Select
ActiveSheet.Paste
Sh.Range("f6:l13").Clear
Sh.Range("a1").Select
Else
If Range("A4000").Value = "" Then
Exit Sub
Else
Sh.Range("A4000").CurrentRegion.Copy
Sh.Range("f6").Select
ActiveSheet.Paste
Sh.Range("A4000").CurrentRegion.Clear
End If


End If


End If
Application.ScreenUpdating = True
End Sub



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best 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

Hi Sanjay,

The code must be in the workbook's private module (double click ThisWorkbook in the visual basic editor)

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best 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