Microsoft Excel - vba help: only part of is executed, why?

Hi,
I'm not too good at vba but have ot start using it more now to get the spreadsheet do what I need.

I need the spreadsheet to go to cell D3 if cell C3 say MFL and to C4 if cell C3 says Humanities : my code only do 1 of those :(

then I need the rows 71:91 to be hidden if cell c3 says anything else than English, Maths or Science

and then I need to be able to write long comments in merged cells in row: 13,15,17,27,29, etc and if text is long I need it to be wrapped automatically. (for now it works if I name 1 merged cell Expand_Row )

i tried to find some codes on net so they're not mine but they work if only 1 of them is there but if i want them all to work they dont.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim C3 As Range, F3 As Range, J3 As Range, D3 As Range
Set C3 = Range("C3")
Set D3 = Range("D3")
Set F3 = Range("F3")
Set J3 = Range("J3")
Dim MergeWidth As Single
Dim cM As Range
Dim AutoFitRng As Range
Dim CWidth As Double
Dim NewRowHt As Double
Dim str01 As String
str01 = "Expand_Row"

  If Not Intersect(Target, Range(str01)) Is Nothing Then
    Application.ScreenUpdating = False
    On Error Resume Next
    Set AutoFitRng = Range(Range(str01).MergeArea.Address)

    With AutoFitRng
    .MergeCells = False
    CWidth = .Cells(1).ColumnWidth
    MergeWidth = 0
    For Each cM In AutoFitRng
      cM.WrapText = True
      MergeWidth = cM.ColumnWidth + MergeWidth
    Next
    'small adjustment to temporary width
    MergeWidth = MergeWidth + AutoFitRng.Cells.Count * 0.66
    .Cells(1).ColumnWidth = MergeWidth
    .EntireRow.AutoFit
    NewRowHt = .RowHeight
    .Cells(1).ColumnWidth = CWidth
    .MergeCells = True
    .RowHeight = NewRowHt
    End With
    Application.ScreenUpdating = True
  End If
 
If Intersect(Target, C3) Is Nothing Then Exit Sub
Application.EnableEvents = False
D3 = ""
F3 = ""
J3 = ""

Application.EnableEvents = True

If Worksheets("CLASS_ANALYSIS").Range("C3").Value = "Humanities" Then _
   MsgBox ("Please select appropriate TA subject in cell C4")
               Range("C4").Select
      If Worksheets("CLASS_ANALYSIS").Range("C3").Value <> "Humanities" Then Range("C4") = ""

If Worksheets("CLASS_ANALYSIS").Range("C3").Value = "MFL" Then _
   MsgBox ("For subject analysis please select appropriate subject in cell D3, leave D3 BLANK for Teacher or class analysis")
               Range("D3").Select
      If Worksheets("CLASS_ANALYSIS").Range("C3").Value <> "MFL" Then Range("D3") = ""

If Target.Address = "$C$3" And UCase(Target.Value <> ("English")) Then
      Rows("71:91").Hidden = True
    Else
      Rows("71:91").Hidden = False
   
End If

If Target.Address = "$C$3" And UCase(Target.Value <> ("Maths")) Then
      Rows("71:91").Hidden = True
    Else
      Rows("71:91").Hidden = False
   
End If
If Target.Address = "$C$3" And UCase(Target.Value <> ("Science")) Then
      Rows("71:91").Hidden = True
    Else
      Rows("71:91").Hidden = False
   
End If

End Sub

I think your issue (at least the major one) is here:

UCase(Target.Value <> ("Maths"))

It should be this are part of your "IF" clause below

(UCase(Target.Value) <> "MATHS")

As for the other stuff, use the "Macro Recorder" feature in Excel and Word to learn the VBA code to perform a given task.  Turn the record on, manually perform the task you are after, turn the recorder off, and then look at the VBA code it generated.  This almost always gives you the head start you need and tweak from there.

Hello Ewa,

Your problem with the cell selection was that the select statements were outside the If construct.  The problem with the row hiding was that after the cell selection stage the Target was no longer C3 (either C4 or D3).  I don't quite understand the merging/text wrap but I wonder whether you could get around it by naming and unnaming the cells inside the macro.  I will think about that bit some more.  I also included the Option Compare Text so that you don't need the Ucase statement.  Here is the modified code for the other two problems.  I have changed the sheet name so that I could test it.  You will just need to change them back.

Regards
Harry

Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

Dim C3 As Range, F3 As Range, J3 As Range, D3 As Range
Set C3 = Range("C3")
Set D3 = Range("D3")
Set F3 = Range("F3")
Set J3 = Range("J3")
Dim MergeWidth As Single
Dim cM As Range
Dim AutoFitRng As Range
Dim CWidth As Double
Dim NewRowHt As Double
Dim str01 As String
str01 = "Expand_Row"

  If Not Intersect(Target, Range(str01)) Is Nothing Then
    Application.ScreenUpdating = False
    On Error Resume Next
    Set AutoFitRng = Range(Range(str01).MergeArea.Address)

    With AutoFitRng
    .MergeCells = False
    CWidth = .Cells(1).ColumnWidth
    MergeWidth = 0
    For Each cM In AutoFitRng
    cM.WrapText = True
    MergeWidth = cM.ColumnWidth + MergeWidth
    Next
    'small adjustment to temporary width
    MergeWidth = MergeWidth + AutoFitRng.Cells.Count * 0.66
    .Cells(1).ColumnWidth = MergeWidth
    .EntireRow.AutoFit
    NewRowHt = .RowHeight
    .Cells(1).ColumnWidth = CWidth
    .MergeCells = True
    .RowHeight = NewRowHt
    End With
    Application.ScreenUpdating = True
  End If
 
If Intersect(Target, C3) Is Nothing Then Exit Sub
Application.EnableEvents = False
D3 = ""
F3 = ""
J3 = ""

Application.EnableEvents = True

If Worksheets("Sheet1").Range("C3").Value = "Humanities" Then
   MsgBox ("Please select appropriate TA subject in cell C4")
     Range("C4").Select
Else
    If Worksheets("Sheet1").Range("C3").Value <> "Humanities" Then Range("C4") = ""
End If
If Worksheets("Sheet1").Range("C3").Value = "MFL" Then
   MsgBox ("For subject analysis please select appropriate subject in cell D3, leave D3 BLANK for Teacher or class analysis")
     Range("D3").Select
Else
    If Worksheets("Sheet1").Range("C3").Value <> "MFL" Then Range("D3") = ""
End If
If (Range("C3").Value <> "English" And Range("C3").Value <> "Maths" _
And Range("C3").Value <> "Science") Then
    Rows("71:91").Hidden = True
    Else
    Rows("71:91").Hidden = False
   
End If

End Sub

Hello again Ewa,
I had a look at your merged cell question and this code might help get you on your way.  There is only the two lines needing change to your current code.  You will have to extend the range of row numbers to what you need, the ones I used were merely for testing.
If ((Target.Row = 20 Or Target.Row = 22) And Target.MergeCells = True) Then
'  If Not Intersect(Target, Range(str01)) Is Nothing Then
    Application.ScreenUpdating = False
    On Error Resume Next
    Set AutoFitRng = Range(Target.MergeArea.Address)

    With AutoFitRng
    .MergeCells = False
    CWidth = .Cells(1).ColumnWidth
    MergeWidth = 0
    For Each cM In AutoFitRng
    cM.WrapText = True
    MergeWidth = cM.ColumnWidth + MergeWidth
    Next
    'small adjustment to temporary width
    MergeWidth = MergeWidth + AutoFitRng.Cells.Count * 0.66
    .Cells(1).ColumnWidth = MergeWidth
    .EntireRow.AutoFit
    NewRowHt = .RowHeight
    .Cells(1).ColumnWidth = CWidth
    .MergeCells = True
    .RowHeight = NewRowHt
    End With
    Application.ScreenUpdating = True
  End If
 
Regards
Harry

brilliant! you're a start, Thank you so much,
Ewa

brilliant! you're a star, Thank you so much,
Ewa 

sorry, Could you explain what I am doing wrong please?
I tried to change the part about hidding rows a little bit so it hides rows 79:98 if C3 = "Eng.Lang" and it doesnt work for me, I am still missing something but have no idea what it is (i checked the brackets this time but they seem ok to me)
thank you
Ewa


Option Explicit
 Option Compare Text
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim C3 As Range, F3 As Range, J3 As Range, D3 As Range, N6 As Range
 Set C3 = Range("C3")
 Set D3 = Range("D3")
 Set F3 = Range("F3")
 Set J3 = Range("J3")
 Set N6 = Range("N6")

If Intersect(Target, C3) Is Nothing Then Exit Sub
 Application.EnableEvents = True
 D3 = ""
 F3 = ""
 J3 = ""
 
 If (Range("C3").Value <> "Eng. & Eng Lang" And Range("C3").Value <> "Eng. Lit" And Range("C3").Value <> "Eng. Lang." _
 And Range("C3").Value <> "Maths" And Range("C3").Value <> "Science Core") Then
   Rows("79:98").Hidden = True
   Else
   Rows("79:98").Hidden = False
End If
  

  If Range("N6").Value <> "Gcse" Then
    Application.Calculation = xlAutomatic
   Rows("7:179").Hidden = True
   Rows("180:350").Hidden = False
   Else
     Application.Calculation = xlAutomatic
     Rows("7:179").Hidden = False
   Rows("180:350").Hidden = True
   End If
    

End Sub

Hello Ewa,
It is a little unclear as to what you want.  At the moment all five of the conditions inside your If statement have to be true for the rows to be hidden.  I assume that it is working correctly for a lesser number of subjects and that it fails when you tried to add to the list.
One possibility that I can see at the moment is that in your text you cite "Eng.Lang" but in your code you have "Eng. Lang." and if you are comparing those then that particular comparison will never be False.
The other thing is in your text you say that you want the rows to be hidden if C3 equals "Eng.Lang" yet the test that you are applying is not equals.  This is why I am a bit confused about what you want.
So could you give the whole list of subjects that are possible to be selected in C3 and a list of those for which you want the rows to be hidden?
One other thing about your code is that you go to the trouble of writing Dim statements for a number of variables and setting them to a Range and then use the Range("XX") construct later in your code.  It's not a problem just a slight waste of effort.
Regards
Harry

hidding rows.zip
sorry Harry, I meant Eng. Lang.  and that those rows shodul be hidden if C3 is different than the given conditions.
it doesnt work for lesser number of subjects.
Thank you for the hint about Dim statement, I will get  better with vba , just need time (lots of it).
I attached the spreadsheet with the code, should be easier to see what it is I'm trying to get
Thank you

Hello Ewa,
Your problem is in this part of the code.


  If Range("N6").Value <> "gcse" Then
    Application.Calculation = xlAutomatic
   Rows("7:179").Hidden = True
   Rows("180:350").Hidden = False
   Else
     Application.Calculation = xlAutomatic
     Rows("7:179").Hidden = False
   Rows("180:350").Hidden = True
   End If

The value in C6 = 'gcse' so the code goes to the else statement and unhides rows 7:179 which includes the rows that you had carefully hidden based on the contents of C3.
Hope this helps you sort it out.  I am happy to help more if you need it.
Regards
Harry

OMG! now I understand and got it working, Thank you so much Harry!