|
If / Else Code (hiding rows based on cell value)
The problem is could be due to you having two dots in front of ..rows
instead of one .rows The x & y are from the case. The code is designed to work as is. I just retested and it worked fine. Send your workbook if you like. -- Don Guillett SalesAid Software "jjones" wrote in message ... Please forgive my ignorance, but where you have ..Rows(x).Hidden = True ..Rows(y).Hidden = True what goes in front of the ellipses? As written it gives a compile error/syntax error, so I'm guessing you meant for me to plug something in there. Also, could you briefly explain to me what this code is really saying in laymen's terms? I "sort of" get it, but then again I'm not really sure I totally understand the x's and y's. Dumb blonde trying to code, jj "Don Guillett" wrote: If I am to assume that you only want certain rows hidden then perhaps you should unhide all and then just hide the desired rows with this instead Sub Hideifj1() With Sheets("Approved Rate Review (2)") .Rows.Hidden = False Select Case UCase(.Range("j1")) Case Is = "RETAIL", "MOTO": x = "17": y = "17" Case Is = "SMALL TICKET": x = "17:19": y = "17" Case Is = "INTERCHANGE PLUS", "APPROVED RATE REVIEW": x = "17:22": y = "27:32" Case Is = "DIAL PAY": x = "17:18": y = "26:33" Case Else MsgBox "None to Hide" End Select ..Rows(x).Hidden = True ..Rows(y).Hidden = True End With -- Don Guillett SalesAid Software "jjones" wrote in message ... I'm still a newbie at writing macros, but here's the situation. I've successfully mapped data from an Access database over to a form letter in Excel. The verbiage in the letter varies depending on the industry type selected (options a Retail, Small Ticket, MOTO, Interchange Plus, Dial Pay). This is stored in cell J1. Since some paragraphs are shorter than others, I sometimes end up with extra blank rows between paragraphs depending on which option has been chosen. As part of my Auto_Open macro, I want these extra rows of blank space hidden depending on the value in J1. As I have the code written now, however, the next if statement overrides the previous one, making all but the last one irrelevant really. I've tried changing it a couple of different ways but I just end up with compile errors. Anybody know how to make this work? I'm open to other ideas if this can't be accomplished with If statements. Here's the code I have now: If Range("J1").Value = "Retail" Then Sheets("Approved Rate Review (2)").Rows("17").EntireRow.Hidden = True Else Sheets("Approved Rate Review (2)").Rows("17").EntireRow.Hidden = False End If If Range("J1").Value = "Small Ticket" Then Sheets("Approved Rate Review (2)").Rows("17:19").EntireRow.Hidden = True Else Sheets("Approved Rate Review (2)").Rows("17:19").EntireRow.Hidden = False End If If Range("J1").Value = "MOTO" Then Sheets("Approved Rate Review (2)").Rows("17").EntireRow.Hidden = True Else Sheets("Approved Rate Review (2)").Rows("17").EntireRow.Hidden = False End If If Range("J1").Value = "Interchange Plus" Then Sheets("Approved Rate Review (2)").Rows("17:22").EntireRow.Hidden = True Sheets("Approved Rate Review (2)").Rows("27:29").EntireRow.Hidden = True Else Sheets("Approved Rate Review (2)").Rows("17:22").EntireRow.Hidden = False Sheets("Approved Rate Review (2)").Rows("27:29").EntireRow.Hidden = False End If If Range("J1").Value = "Dial Pay" Then Sheets("Approved Rate Review (2)").Rows("17:18").EntireRow.Hidden = True Sheets("Approved Rate Review (2)").Rows("26:33").EntireRow.Hidden = True Else Sheets("Approved Rate Review (2)").Rows("17:18").EntireRow.Hidden = False Sheets("Approved Rate Review (2)").Rows("26:33").EntireRow.Hidden = False End If |
If / Else Code (hiding rows based on cell value)
I suspect you are right in that somewhere between me and the OP the extra dot gets added. When I sent it, it WAS lined up with the dot above. I will certainly try to remember to move it over before transmitting. However, I'm getting old so....... -- Don Guillett SalesAid Software "Tom Ogilvy" wrote in message ... That's funny Don. Just a heads up that your original answer is what introduced the two dots in front of the .row (maybe it is hidden to you, but it certainly isn't to me and apparently the OP. ) This seems to be a common occurance in your postings. Perhaps you should note that you should never have a period in the leftmost column. I know in some cases it is seen as a formatting character and I suspect your newsreader or whatever is adding a second period to mark it as not a formatting character. for example, the problem didn't occur here With Sheets("Approved Rate Review (2)") .Rows.Hidden = False since the period was actually in the second column (preceded by a space). -- Regards, Tom Ogilvy "Don Guillett" wrote: The problem is could be due to you having two dots in front of ..rows instead of one .rows The x & y are from the case. The code is designed to work as is. I just retested and it worked fine. Send your workbook if you like. -- Don Guillett SalesAid Software "jjones" wrote in message ... Please forgive my ignorance, but where you have ..Rows(x).Hidden = True ..Rows(y).Hidden = True what goes in front of the ellipses? As written it gives a compile error/syntax error, so I'm guessing you meant for me to plug something in there. Also, could you briefly explain to me what this code is really saying in laymen's terms? I "sort of" get it, but then again I'm not really sure I totally understand the x's and y's. Dumb blonde trying to code, jj "Don Guillett" wrote: If I am to assume that you only want certain rows hidden then perhaps you should unhide all and then just hide the desired rows with this instead Sub Hideifj1() With Sheets("Approved Rate Review (2)") .Rows.Hidden = False Select Case UCase(.Range("j1")) Case Is = "RETAIL", "MOTO": x = "17": y = "17" Case Is = "SMALL TICKET": x = "17:19": y = "17" Case Is = "INTERCHANGE PLUS", "APPROVED RATE REVIEW": x = "17:22": y = "27:32" Case Is = "DIAL PAY": x = "17:18": y = "26:33" Case Else MsgBox "None to Hide" End Select ..Rows(x).Hidden = True ..Rows(y).Hidden = True End With -- Don Guillett SalesAid Software "jjones" wrote in message ... I'm still a newbie at writing macros, but here's the situation. I've successfully mapped data from an Access database over to a form letter in Excel. The verbiage in the letter varies depending on the industry type selected (options a Retail, Small Ticket, MOTO, Interchange Plus, Dial Pay). This is stored in cell J1. Since some paragraphs are shorter than others, I sometimes end up with extra blank rows between paragraphs depending on which option has been chosen. As part of my Auto_Open macro, I want these extra rows of blank space hidden depending on the value in J1. As I have the code written now, however, the next if statement overrides the previous one, making all but the last one irrelevant really. I've tried changing it a couple of different ways but I just end up with compile errors. Anybody know how to make this work? I'm open to other ideas if this can't be accomplished with If statements. Here's the code I have now: If Range("J1").Value = "Retail" Then Sheets("Approved Rate Review (2)").Rows("17").EntireRow.Hidden = True Else Sheets("Approved Rate Review (2)").Rows("17").EntireRow.Hidden = False End If If Range("J1").Value = "Small Ticket" Then Sheets("Approved Rate Review (2)").Rows("17:19").EntireRow.Hidden = True Else Sheets("Approved Rate Review (2)").Rows("17:19").EntireRow.Hidden = False End If If Range("J1").Value = "MOTO" Then Sheets("Approved Rate Review (2)").Rows("17").EntireRow.Hidden = True Else Sheets("Approved Rate Review (2)").Rows("17").EntireRow.Hidden = False End If If Range("J1").Value = "Interchange Plus" Then Sheets("Approved Rate Review (2)").Rows("17:22").EntireRow.Hidden = True Sheets("Approved Rate Review (2)").Rows("27:29").EntireRow.Hidden = True Else Sheets("Approved Rate Review (2)").Rows("17:22").EntireRow.Hidden = False Sheets("Approved Rate Review (2)").Rows("27:29").EntireRow.Hidden = False End If If Range("J1").Value = "Dial Pay" Then Sheets("Approved Rate Review (2)").Rows("17:18").EntireRow.Hidden = True Sheets("Approved Rate Review (2)").Rows("26:33").EntireRow.Hidden = True Else Sheets("Approved Rate Review (2)").Rows("17:18").EntireRow.Hidden = False Sheets("Approved Rate Review (2)").Rows("26:33").EntireRow.Hidden = False End If |
If / Else Code (hiding rows based on cell value)
See Tom's posting and my answer about the dots. Try to remember that when
you have a with statement you must have ONE dot preceeding the statement referring to the with. Look in vba help index for WITH Yes, the x and y are simply variables. You could have called them jj1 and jj2 and if you had a third you just use z or jj3. Instead of doing it the way I did I could have used the example below. Case Is = "SMALL TICKET": x = "17:19": y = "17" Case Is = "SMALL TICKET": rows("17:19").hidden=true notice I didn't need the y here cuz no demand for it. and Case Is = "DIAL PAY": x = "17:18": y = "26:33" Case Is = "DIAL PAY": rows("17:18").hidden=true:rows("26:33").hidden=tru e All I did was unhide all the rows each time the macro fired and then hide based on the criteria. Select case is better for this sort of thing than IF, especially when it is easy to include more criteria per case. -- Don Guillett SalesAid Software "jjones" wrote in message ... Thanks, Don! I took out the extra dots and it works like a charm. :) I still don't get the x & y thing though--are those like coordiantes or just variables representing add'l instructions (i.e. if I had a third set of rows to hide would there be a "z")??? I mean I'm thrilled that this works, but I just wish I completely understood it. I'd like to be able to use it again in the future without having to post more questions (although you've been kind enough to answer pretty quickly). Thanks again, JJ (dumb blonde that LOOKS really smart to boss now) ;) "Don Guillett" wrote: The problem is could be due to you having two dots in front of ..rows instead of one .rows The x & y are from the case. The code is designed to work as is. I just retested and it worked fine. Send your workbook if you like. -- Don Guillett SalesAid Software "jjones" wrote in message ... Please forgive my ignorance, but where you have ..Rows(x).Hidden = True ..Rows(y).Hidden = True what goes in front of the ellipses? As written it gives a compile error/syntax error, so I'm guessing you meant for me to plug something in there. Also, could you briefly explain to me what this code is really saying in laymen's terms? I "sort of" get it, but then again I'm not really sure I totally understand the x's and y's. Dumb blonde trying to code, jj "Don Guillett" wrote: If I am to assume that you only want certain rows hidden then perhaps you should unhide all and then just hide the desired rows with this instead Sub Hideifj1() With Sheets("Approved Rate Review (2)") .Rows.Hidden = False Select Case UCase(.Range("j1")) Case Is = "RETAIL", "MOTO": x = "17": y = "17" Case Is = "SMALL TICKET": x = "17:19": y = "17" Case Is = "INTERCHANGE PLUS", "APPROVED RATE REVIEW": x = "17:22": y = "27:32" Case Is = "DIAL PAY": x = "17:18": y = "26:33" Case Else MsgBox "None to Hide" End Select ..Rows(x).Hidden = True ..Rows(y).Hidden = True End With -- Don Guillett SalesAid Software "jjones" wrote in message ... I'm still a newbie at writing macros, but here's the situation. I've successfully mapped data from an Access database over to a form letter in Excel. The verbiage in the letter varies depending on the industry type selected (options a Retail, Small Ticket, MOTO, Interchange Plus, Dial Pay). This is stored in cell J1. Since some paragraphs are shorter than others, I sometimes end up with extra blank rows between paragraphs depending on which option has been chosen. As part of my Auto_Open macro, I want these extra rows of blank space hidden depending on the value in J1. As I have the code written now, however, the next if statement overrides the previous one, making all but the last one irrelevant really. I've tried changing it a couple of different ways but I just end up with compile errors. Anybody know how to make this work? I'm open to other ideas if this can't be accomplished with If statements. Here's the code I have now: If Range("J1").Value = "Retail" Then Sheets("Approved Rate Review (2)").Rows("17").EntireRow.Hidden = True Else Sheets("Approved Rate Review (2)").Rows("17").EntireRow.Hidden = False End If If Range("J1").Value = "Small Ticket" Then Sheets("Approved Rate Review (2)").Rows("17:19").EntireRow.Hidden = True Else Sheets("Approved Rate Review (2)").Rows("17:19").EntireRow.Hidden = False End If If Range("J1").Value = "MOTO" Then Sheets("Approved Rate Review (2)").Rows("17").EntireRow.Hidden = True Else Sheets("Approved Rate Review (2)").Rows("17").EntireRow.Hidden = False End If If Range("J1").Value = "Interchange Plus" Then Sheets("Approved Rate Review (2)").Rows("17:22").EntireRow.Hidden = True Sheets("Approved Rate Review (2)").Rows("27:29").EntireRow.Hidden = True Else Sheets("Approved Rate Review (2)").Rows("17:22").EntireRow.Hidden = False Sheets("Approved Rate Review (2)").Rows("27:29").EntireRow.Hidden = False End If If Range("J1").Value = "Dial Pay" Then Sheets("Approved Rate Review (2)").Rows("17:18").EntireRow.Hidden = True Sheets("Approved Rate Review (2)").Rows("26:33").EntireRow.Hidden = True Else Sheets("Approved Rate Review (2)").Rows("17:18").EntireRow.Hidden = False Sheets("Approved Rate Review (2)").Rows("26:33").EntireRow.Hidden = False End If |
| All times are GMT +1. The time now is 12:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com