Self Taught (badly) VBA Help :(

G

Grammarjunkie

Board Regular
Joined
Mar 22, 2016
Messages
86
I know Excel formulas, and I assumed VBA would be the same. It has similarities, but it definitely is its own beast. I was hoping someone could take a look at my VBA code, and just use one little piece as an example as to how to write it ... prettier? I know that's a tall order. But I know the way I have it written can't be the easiest, simplest way.

And also it causes some weird things. Like pressing Enter, instead of it going to the next cell (that in my VBA code is ".Activate"), it jumps to a random one. In one instance it jumped to the cell BEFORE it. I don't know how I accomplished that.

On any account, I'm not asking for anyone to rewrite literally all of it. (Though that would make my day.)

Just a little "VBA for Dummies." As I said, I'm self taught, so I just played around until I got it to do what I wanted. But it's probably not written the most ... logically.

Thanks for even considering it. I appreciate it so much. :)

The following is my code (it's not that long compared to what y'all do, I bet):

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Unprotect
Application.ScreenUpdating = False

If Range("F45") = "Y" Then
    Rows("46").Hidden = False
    Range("F46").Activate
Else
    Rows("46").Hidden = True
End If

If Range("F45") = "y" Then
    Rows("46").Hidden = False
    Range("F46").Activate
Else
    Rows("46").Hidden = True
End If

If Range("F46") = "Y" Then
    Rows("47").Hidden = False
    Range("F47").Activate
Else
    Rows("47").Hidden = True
End If

If Range("F46") = "y" Then
    Rows("47").Hidden = False
    Range("F47").Activate
Else
    Rows("47").Hidden = True
End If


If Range("J3") = "TPO" Then
    Rows("14:15").Hidden = False
Else
    Rows("14:15").Hidden = True
End If

If Range("D13") = "Purchase" Then
    Rows("86").Hidden = False
Else
    Rows("86").Hidden = True
End If

If Range("F102") = "Y" Then
    Rows("103").Hidden = False
    Range("F103").Activate
Else
    Rows("103").Hidden = True
End If

If Range("F102") = "y" Then
    Rows("103").Hidden = False
    Range("F103").Activate
Else
    Rows("103").Hidden = True
End If

If (Range("D6") <> "") And (Range("E18") <> "") And (Range("D6") > Range("E18")) Then
        Rows("39:41").Hidden = False
Else
        Rows("39:41").Hidden = True
End If


If Range("E19") = "" Then
        Rows("48:52").Hidden = True
Else
        Rows("48:52").Hidden = False
End If

If Range("D13") = "Purchase" Then
    Rows("60").Hidden = False
Else
    Rows("60").Hidden = True
End If

If Range("E22") = "" Then
    Rows("53:57").Hidden = True
Else
    Rows("53:57").Hidden = False
End If

If Range("D13") = "Purchase" Then
    Rows("105:114").Hidden = False
Else
    Rows("105:114").Hidden = True
End If

If Range("J3") = "TPO" Then
    Rows("119:122").Hidden = False
Else
    Rows("119:122").Hidden = True
End If

If Range("D13") = "Purchase" Then
    Rows("128:131").Hidden = False
Else
    Rows("128:131").Hidden = True
End If

If Range("J3") = "TPO" Then
    Rows("127").Hidden = False
Else
    Rows("127").Hidden = True
End If

If Range("F96") = "Y" Then
    Rows("97:98").Hidden = False
    Range("F97").Activate
Else
    Rows("97:98").Hidden = True
End If

If Range("F96") = "y" Then
    Rows("97:98").Hidden = False
    Range("F97").Activate
Else
    Rows("97:98").Hidden = True
End If

If Range("F90") = "Y" Then
    Rows("91:96").Hidden = False
    Range("F91").Activate
Else
    Rows("91:96").Hidden = True
End If

If Range("F90") = "y" Then
    Rows("91:96").Hidden = False
    Range("F91").Activate
Else
    Rows("91:96").Hidden = True
End If

If Range("F102") = "Y" Then
    Rows("103").Hidden = False
    Range("F103").Activate
Else
    Rows("103").Hidden = True
End If

If Range("F102") = "y" Then
    Rows("103").Hidden = False
    Range("F103").Activate
Else
    Rows("103").Hidden = True
End If

If Range("F107") = "Y" Then
    Rows("108:109").Hidden = False
    Range("F108").Activate
Else
    Rows("108:109").Hidden = True
End If

If Range("F107") = "y" Then
    Rows("108:109").Hidden = False
    Range("F108").Activate
Else
    Rows("108:109").Hidden = True
End If


Application.ScreenUpdating = True
ActiveSheet.Protect


End Sub
 
Sort by date Sort by votes
Joe4 said:
Aha! The most important think you should take away from this thread is the following:
Merged cells are very, very bad, and you should avoid using them whenever possible!
I am serious. They wreak havoc with things like VBA and sorting. Most serious programmers will not touch them with a ten foot pole!

The good news is you can easily get the exact same visual effect that merged cells gives you without all the problems by using the "Center Across Selection" formatting option. So I would highly recommend removing all of your merged cells and use this format option instead. That should solve your issue.
Click to expand...

Lol! Look at you, Sleuth! :)

I had no idea. Going back and unmerging stuff will take ... a bit.. Admittedly I am a workaholic, so I can come in on a Saturday and play around for some hours. I had no idea that they would affect things so much! My boss also dislikes them. But I didn't know that until after I went Merge-city on this. She is going to love this. Haha.

Well... drat. That explains a bit. Almost everything that's caused me trouble has involved a merge cell. Let's blame it on that and not me! :D

One more thing, and then I'll let you go. I swear. I literally have no more questions. This isn't even a question, so much as it is something isn't working.

Code: 
'       Cell J3
        Case "J3"
            If UCase(Target) = "TPO" Then
                Range("14:15, 117:120, 125:125").EntireRow.Hidden = False
            Else
                Range("14:15, 117:120, 125:125").EntireRow.Hidden = True
            End If
'       Cell D13
        Case "D13"
            If UCase(Target) = "Purchase" Then
                Range("126:129, 103:112, 58:58, 85:85").EntireRow.Hidden = False
            Else
                Range("126:129, 103:112, 58:58, 85:85").EntireRow.Hidden = True
            End If


These are the exact same codes. Doing the exact same thing. The TPO (J3) one works like a charm. The Purchase (D13) one isn't working even a little bit. And I can't figure out why. J3 and D13 are both dropdowns (though I've tried doing D13 as not a dropdown, too), the formatting is the same.
It isn't merged! :D

So I don't quite understand why rows aren't unhiding when "Purchase" is filled in. Especially when TPO is the exact same code.
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Joe4 said:
Aha! The most important think you should take away from this thread is the following:
Merged cells are very, very bad, and you should avoid using them whenever possible!
I am serious. They wreak havoc with things like VBA and sorting. Most serious programmers will not touch them with a ten foot pole!

The good news is you can easily get the exact same visual effect that merged cells gives you without all the problems by using the "Center Across Selection" formatting option. So I would highly recommend removing all of your merged cells and use this format option instead. That should solve your issue.
Click to expand...
If I may, sometimes for visual aid they are kind of necessary, if you really need to work with merged cells, probably the best approaches are to work with the following functions


Code: 
 Function IsMerged(AnyRange As range) As Boolean
'I left it as it is, I took the code from somewhere else
   Dim MergedRegion As range
   IsMerged = AnyRange.MergeCells
   If IsMerged Then Set MergedRegion = AnyRange.MergeArea Else Set MergedRegion = AnyRange
   Debug.Print MergedRegion.Rows.Count; " x "; MergedRegion.Columns.Count
   Debug.Print MergedRegion.Cells.Address
End Function 


Function GetMergedCellValue(iRow As Integer, iColumn As Integer) As String
'I left it as it is, I took the code from somewhere else
 Dim mRange As Excel.range, sReturnString As String
 Set mRange = ActiveSheet.Cells(iRow, iColumn).MergeArea
 sReturnString = mRange.Cells(1, 1).Value
GetMergedCellValue = sReturnString
End Function
Both functions work for any cell in the merged range
IE: If Range("E19:F25") is merged you can do the following (pasting the previous codes)
Code: 
Sub Test()
Dim ValueInMergedCell as Variant
If IsMerged(Range("E19")) = True Then ValueInMergedCell = GetMergedCellValue(19,5) 'cells 19,5 = "E19"
End Sub
 
Last edited:
Upvote 0
If I may, sometimes for visual aid they are kind of necessary
Click to expand...
Have you tried "Center Across Selection"?
As long as you are just merge cells across columns (and not down rows), "Center Across Selection" WILL give you the same "visual aid" without the issues or without having to jump through any hoops.
From the description given by GrammarJunkie, it sounds like he is merged across columns, so I think it should work for him.
 
Upvote 0
Joe4 said:
Have you tried "Center Across Selection"?
As long as you are just merge cells across columns (and not down rows), "Center Across Selection" WILL give you the same "visual aid" without the issues or without having to jump through any hoops.
From the description given by GrammarJunkie, it sounds like he is merged across columns, so I think it should work for him.
Click to expand...

It definitely fit the situation just the same for me. I hardly ever have a need to merge across columns AND rows.
 
Upvote 0
Joe4 said:
Have you tried "Center Across Selection"?
As long as you are just merge cells across columns (and not down rows), "Center Across Selection" WILL give you the same "visual aid" without the issues or without having to jump through any hoops.
From the description given by GrammarJunkie, it sounds like he is merged across columns, so I think it should work for him.
Click to expand...

Also I hope you saw my last question.. Regarding the D13 and J3. It was just above his comment.

Sorry if that sounds pushy. I just know it's easy to miss stuff in a wall of text.
 
Upvote 0
I actually did miss it completely.

The TPO (J3) one works like a charm. The Purchase (D13) one isn't working even a little bit.
Click to expand...
I have seen things like extra spaces at the end of the entry mess things up.
If you select "Purchase", try entering this formula somewhere and see what it returns:
Code: 
=LEN(D13)
 
Upvote 0
Joe4 said:
I actually did miss it completely.


I have seen things like extra spaces at the end of the entry mess things up.
If you select "Purchase", try entering this formula somewhere and see what it returns:
Code: 
=LEN(D13)
Click to expand...

... The number eight. "8".

Which I have no idea what that means. Haha
 
Upvote 0
It is just saying that the length of the value in that cell is 8. Since "Purchase" is 8 letters long, that would appear correct.

We do not have any other Cases already looking at D13, do we?
If you have two, they could be interfering with each other.

If you post your code, as you currently have it, I could test it out on my side and see if I see anything that looks odd.
 
Upvote 0
Joe4 said:
It is just saying that the length of the value in that cell is 8. Since "Purchase" is 8 letters long, that would appear correct.

We do not have any other Cases already looking at D13, do we?
If you have two, they could be interfering with each other.

If you post your code, as you currently have it, I could test it out on my side and see if I see anything that looks odd.
Click to expand...

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to make sure only one cell has been updated (otherwise exit)
    If Target.Count > 1 Then Exit Sub
    
    ActiveSheet.Unprotect
    Application.ScreenUpdating = False

'   Decide what to do based on which cell was updated
    Select Case Replace(Target.Address, "$", "")
    
'       Cell D13
        Case "D13"
            If UCase(Target) = "Purchase" Then
                Range("126:129, 103:112, 58:58, 85:85").EntireRow.Hidden = False
            Else
                Range("126:129, 103:112, 58:58, 85:85").EntireRow.Hidden = True
            End If

'       Cell F45
        Case "F45"
            If UCase(Target) = "Y" Then
                Rows("46").Hidden = False
                Range("F46").Activate
            Else
                Rows("46").Hidden = True
            End If
            
'       Cell F46
        Case "F46"
            If UCase(Target) = "Y" Then
                Rows("47").Hidden = False
                Range("F47").Activate
            Else
                Rows("47").Hidden = True
            End If
            
'       Cell F100
        Case "F100"
            If UCase(Target) = "Y" Then
                Rows("101").Hidden = False
                Range("F101").Activate
            Else
                Rows("101").Hidden = True
            End If

'       Cell J3
        Case "J3"
            If UCase(Target) = "TPO" Then
                Range("14:15, 117:120, 125:125").EntireRow.Hidden = False
            Else
                Range("14:15, 117:120, 125:125").EntireRow.Hidden = True
            End If
            
'       Cell F105
        Case "F105"
            If UCase(Target) = "Y" Then
                Rows("106:107").Hidden = False
                Range("F106").Activate
            Else
                Rows("106:107").Hidden = True
            End If

'       Cell F88
        Case "F88"
            If UCase(Target) = "Y" Then
                Range("89:91, 94:94").EntireRow.Hidden = False
                Range("F89").Activate
            Else
                Range("89:91, 94:94").EntireRow.Hidden = True
            End If
            
'       Cell F94
        Case "F94"
            If UCase(Target) = "Y" Then
                Rows("95").Hidden = False
                Range("F95").Activate
            Else
                Rows("95").Hidden = True
            End If
        
'       Cell F95
        Case "F95"
            If UCase(Target) = "Y" Then
                Rows("96").Hidden = False
                Range("F96").Activate
            Else
                Rows("96").Hidden = True
            End If

'       Cell E22
        Case "E22"
            If UCase(Target) = "" Then
                Rows("52:55").Hidden = True
            Else
                Rows("52:55").Hidden = False
            End If
            
'       Cell E19
        Case "E19"
            If UCase(Target) <> "" Then
                Rows("48:51").Hidden = False
            Else
                Rows("48:51").Hidden = True
            End If
            
'       Cell F73
        Case "F73"
            If UCase(Target) = "Y" Then
                Rows("74:75").Hidden = False
                Range("F74").Activate
            Else
                Rows("74:75").Hidden = True
            End If
            
            If UCase(Target) = "N" Then
                Rows("76:78").Hidden = False
                Range("F76").Activate
            Else
                Rows("76:78").Hidden = True
            End If
            
'       Cell F75
        Case "F75"
            If UCase(Target) = "N" Then
                Rows("76:78").Hidden = False
                Range("F76").Activate
            Else
                Rows("76:78").Hidden = True
            End If
            
'       Cell D6 And E18
        Case "D6", "E18"
            If (UCase(Range("D6"))) > (UCase(Range("E18"))) Then
                Rows("39:41").Hidden = False
            Else
                Rows("39:41").Hidden = True
            End If


End Select

 Application.ScreenUpdating = True
    ActiveSheet.Protect

End Sub
 
Upvote 0
Oh my gosh, we have been looking at this for so long that we were overlooking the obvious.
What does the UCase function do? Converts it to ALL CAPS.

So UCase(Target) can NEVER equal "Purchase".
It CAN equal "PURCHASE".

So your line of code has to be:
Code: 
            If UCase(Target) = "PURCHASE" Then
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Copy and unfilter in VBA
Replies
1
Views
173
Alex Blakenburg
A
J
  • Question Question
Combining VBA code together...need help!
Replies
3
Views
378
Fluff
Fluff
Rob_010101
  • Question Question
Mail Merge Macro - Saves to PDF/Word
Replies
1
Views
704
Rob_010101
Rob_010101
R
  • Solved
VBA Timer
2
Replies
11
Views
1K
Rana Gray
R
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
325
STARL0RD
S

Forum statistics

Threads
1,223,060
Messages
6,169,860
Members
452,286
Latest member
noclue2000

Share this page

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
Back
Top