hi ^^
okay so am creating a template with a lot of catergoies and fill in spots for each one. But for each form filled out not all caterogies are need so some would be Not applicable, hhowever their are still the spaces and rows below the catoergies that I want to delete. So I turned one cell into a dropdown box where it's either blank or Not applicable. If its blank its find all the cells below stay because we are using the catoergy. If it's Nto applicable I want to delete the following 4 or 5 cells beneth the catoergy heading. If this possible.
**note I already have a auto fit code in place so am adding on to it :
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
Dim ProtectStatus As Boolean
With Target
If .MergeCells And .WrapText Then
ProtectStatus = Me.ProtectContents
If ProtectStatus Then Me.Unprotect ' "password"
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeAreaw
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
On Error Resume Next
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
On Error GoTo 0
Application.ScreenUpdating = True
If ProtectStatus Then Me.Protect ' "password"
If Range("NOTA") Is NotApplicable Then Run "NA"
End If
End With
End Sub
the NOTA is the cells that ae montiored to see if it is blank or NotApplicable. orginially I was using a macro but I couldnt get it to delete the right number of cells in the right place..Help and thxs in advance.
Bookmarks