Hiding a column VBA based on a condition

Im attempting to hide any coumn based on a condition.

For example should 000 exist in cells B1:F9 then I want to hide
the corresponding column say coumn C.

b      c     d       e       f     

1               000
2
3
4
5
6
7
8
9 


Can anyone work out the code and also whats the best way to teac
yourself VBA. At the moment Im learning but very slowly can anyon
recommend anything.

Thanks for your time.


Chri

--
Message posted from http://www.ExcelForum.com

0
5/17/2004 7:12:59 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
201 Views

Similar Articles

[PageSpeed] 39

Here is a couple of ways


1st macro looks for only match using find command on the renge

2nd macro looks at the value of each cell in the range

Sub Macro1()
Dim i As Integer
On Error Resume Next
i = Sheets("Sheet1").Range("b1:f9").Find(What:="000", _
After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:= _
False).Column
On Error GoTo 0
If i > 0 Then
Sheets("sheet1").Columns(i).EntireColumn.Hidden = True
End If
End Sub

Sub Macro2()
Dim Rng As Range
For Each Rng In Sheets("Sheet1").Range("b1:f9")
If Rng.EntireColumn.Hidden = False Then
If Rng.Value = "000" Then
Sheets("sheet1").Columns(Rng.Column).EntireColumn.Hidden = True
End If
End If
Next
End Su

--
Message posted from http://www.ExcelForum.com

0
5/17/2004 7:31:20 AM
Reply:

Similar Artilces:

how do I remove Carriage Returns from a column in a spreadsheet?
I have some data that I am manipulating in Excel to do an export to another application. I need to be able to do a universal find and replace to get rid of instances of Carriage Returns and Line Feeds within the data. I have tried the basic find and replace functions but I don't know how to make them find a carriage return character. Any help would be greatly apreciated. Doug You could use a 'helper' column and the SUBSTITUTE function This one replaces line feeds with nothing =SUBSTITUTE(C2,CHAR(10),"") Use CHAR(13) for carriage returns You can then Edit>P...

comparing cells in one column to another
I have a column on a spreadsheet and an array of strings. I need to look at the first cell on the spreadsheet and find a matching value in the array of strings. This is how I am doing that With ThisWorkbook.Worksheets(Tracker).Range("Order_Number_Header") For E = 0 To Total_Rows_On_Spreadsheet - 1 For F = 0 To Total_Elements_In_Array - 1 If .Offset(E, 0) = FTS_Order_Number(F) Then msgbox"I Have a Match end if next F next E end with Nothing is matching and I know there are some matches. When I display the value in the fields I see "12345678" for both fields. The f...

Hiding Exchange 2003 user's group membership in Outlook 2003
When a user X sends an email to user Y, user Y can find out all groups user X belongs to (both Security & Distribution Groups and those Groups' members) by simply double clicking on the sender's name in Outlook 2003. This could be serious a security issue in some deployments. Is there a way to hide the groups a user belongs to? I can hide the members of Distribution Groups (user X is a member of) from user Y, by configuring "Hide membership" of those Groups using Exchange Tasks option with AD-Users/Groups tool. However, I cannot hide the members of Security Groups (...

How can I read CSV file using VBA?
I have to read CSV file using VBA. I don't want to use macros or queries like transfer spreadsheet etc. because I want to apply certain rules in a module after reading the file. Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1 I am using the following code but one of my field which has both characters and numbers is not importing at all.. How do I handle this? Sub import_csv() DoCmd.TransferText acImportDelim, "", "Test_CSV", "c:\csv_files\12-31-2009 Test.csv", ...

Counting the number of fields in a column that have data?
I have an Excel document with about 5,300 rows. One of the columns has data entered only into some of the fields, maybe 100 of the 5,300 rows. Is there an easy way to get Excel to count all of the fields in that column that have data entered in it? Thanks! On 1/25/10 4:57 PM, in article eW3gilgnKHA.1548@TK2MSFTNGP02.phx.gbl, "Rick" <someone@yahoo.com> wrote: > I have an Excel document with about 5,300 rows. One of the columns has > data entered only into some of the fields, maybe 100 of the 5,300 rows. > > Is there an easy way to get Excel to cou...

Records with condition
Hi, I have a form "frmbirthsearch" with 2 unbounded objects for birth dates: txtStartDate and txtEndDate with cmdOK command button. How do I write the event procedure (on click) so as when this OK button is clicked the query qryaddress is opened with only the records that are between the 2 mentioned dates? Hello Khalil. Khalil Handal wrote: > Hi, > I have a form "frmbirthsearch" with 2 unbounded objects for birth > dates: txtStartDate and txtEndDate with cmdOK command button. > How do I write the event procedure (on click) so as when this OK > button ...

Copying a column from several similar sheets into one sheet
I have several worksheets in one workbook and I want to merge/copy one column from each worksheet (the same column) (Like this, just imagine more columns with more data on several worksheets Date 06052003 Actual 13 FMT 12 % 87 ) and put these columns into rows so that each worksheet's data will descend vertically (EX: Date Actual FMT % 06052003 13 12 87 06062003 12 15 89 06072003 11 17 90 That's what I want, Please help me to do it. ...

Subject based filtering of DSN messages
I can't make this work for the vast majority of the DSN messages we get (message undeliverable and such). I have setup rules to filter the different bounce messages that I see in subject lines and some of them work fine. Most do not. The messages the come from postfix (for instance) have mime-encoded notification messages, split in several parts. I've tried matching the Subject: line I end up seeing in Outlook and the subject line that is in the internet headers of the message, which is not what outlook shows me. I think exchange 2003 is recognizing these messages and try...

Conditional formatting?
I have rows of different activities listed by description. I now need to allocate numeric codes to those descriptions. There are about 20 different codes I need to use. Is it possible to then total those codes on the bottom PER code (i.e. all codes of 115 total on the bottom in one cell, all codes of 135 total on the bottom in another cell) or do I have to do a separate column for each code? Thanks. Look at SUMIF(). If codes in column A and values in column B: =SUMIF(A1:A100,115,B1:B100) and then =SUMIF(A1:A100,135,B1:B100) You could even have a cell set up to enter the...

Sql Server Indexing With Two or More Columns
I got a question with indexing. If I create an index and select 2 or more columns, what is the difference with that and creating 2 (or more separate ) indexes for them? Thanks mark It depends on what you are doing If you have WHERE Last=@p1 AND First=@p2 there no need to have two indexes , however having WHERE First=@p2 the first index on Last,First may or may not be useful, so in that case having two NCI may be a good idea I mean you need testing it, and make a decision.Also there is no need to create NCI on every column, it is especially true in SQL Server 2005 and onwards w...

How to select query fields based on Combo box?
Greeting, I have a table which has 5 fields, ID, Name, Age, School and Class. I have a combo box which shows the fields of a Query. This combo is located in form which presents all the fields of the table. What I want to do is once the user selects a field for more than one the fields in form shown as selected. Also, I want to make a button for reshow all fields in form. Thank you in advance. ...

SUMMARIZING DATA BASED ON DATES GROUPED IN WEEKS
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C6ED3C.00C7C2B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello! 1) The RAW DATA SHEET is where I maintain a cash receipt register in a = list form on a daily basis. Any date may have multiple entries due to = different customer payment. 2) The SUMMARY SHEET shows how I want to summarize the data contained in = RAW DATA SHEET. Basically the summary sheet is summing based on the date = grouped in weeks. =20 RAW DATA SHEET DATE DAY CUSTOMER AMOUNT=20 ...

Compare objects based on IComparable<T> with unknown T
I need to implement a method that processes two objects. Among other things, the method may want to try to compare the two objects, which should use the CompareTo method, provided the two objects are the same class and the class implements IComparable<T>. void process(object o1, object o2) { if (o1.GetType().Equals(o2.GetType())) { Type t = o1.GetType(); if t implements IComparable<T> where T is not known then ??? compare o1 and o2 using their CompareTo method ??? ...

Hide "Grouped" Controls
Hi Folks - I have 15 controls (labels, text boxes and lines) that I would like to hide for a certain set of criteria. I know I can set each control's visible property to false, but I'm wondering if there is a way to 'group' the controls together, then just hide the group. Any ideas? Thanks. Michael You can use the TAG property and give each of the controls you want to show/hide the same tag. Then you can loop through the controls, check the tag value and show or hide that way. 'UNTESTED AIRCODE (with no error handling) Private Sub sShowStuff(Optional tfShow as ...

hide a text box in a continuous form
I have a form that i created that display each form as a row in a continous form. What I need is to have one text field on one row not visible when a check box is checked. Does anyone know how to do this? Thanks! Using Conditional Formatting, evalaute the state of the field bound to the CheckBox to set the Background color of the TextBox to match that of the Background color of the form. You might have to set the Disabled prop via CF as well. There is sample code showing one wat to accomplish this here: http://www.lebans.com/conditionalformatting.htm A2K or Higher Only! New Feb 08,2002...

How to change existing table record value by VBA and How to add new record in existing table by VBA
Hi all, I got table in my database with name "tbldata" and i have two fields in that table with the name "Ticket_No" and "Amount". In "Ticket_No" filed column i have value "SD001" and in same row of "Amount" column i have figure "50". With VBA how can i lookup for value "SD001" in "Ticket_No" field and change figure "50" to "30" which is appearing in same row of "Amount" column field. My other question is that how can i add new record in same table with VBA. I want...

% as column not Pie
Im trying to create a single column chart to express three values as a percentage of the overall total. It works using the default pie chart but I cant get it to express the values in a vertical single column. Thanks for any tips or help in advance. In the chart wizard, select the 100% Stacked Column chart type, the top left option of the Column chart family. If your three values are aligned horizontally in the sheet, make sure the chart is aligned by columns (or vice versa). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions ht...

Using VBA to change default color palette
We currently have an add-in for Excel Xp which changes the default excel color palette so that it displays our companies corporate color scheme. We are wanting to use this in Excel 2007 as well but cannot get it to work. Included is some of the code used for Excel XP Sub companyPalette() On Error Resume Next 'Modify the first row to compnay Primary color Palette ActiveWorkbook.Colors(11) = RGB(160, 0, 80) ActiveWorkbook.Colors(55) = RGB(225, 110, 0) ActiveWorkbook.Colors(52) = RGB(235, 175, 0) ActiveWorkbook.Colors(51) = RGB(180, 190, 0) ...

conditional formatting:highlight row based on blank or non-blank c
Does anyone know if, and how, is possible to use Conditional Formatting feature to automatically highlight the whole row if a specific cell in that row is non-blank (or blank)? Thank you! Hi, Yes, first select your row then in your conditional formatting select "formula is" from the drop down menu and type: =ISBLANK($A1)=FALSE or =ISBLANK($A1)=TRUE depending on weather you want the condition to apply when your cell is blank or non blank. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum....

i want to set conditional format to cells containing odd numbers
i want to set conditional format to cells containing odd numbers 1. Select the cell/Range (say A1:A10). Please note that the cell reference A1 mentioned in the formula is the active cell in the selection. Active cell will have a white background even after selection 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Formula Is' and enter the below formula =MOD(A1,2) 4. Click Format Button>Pattern and select your color (say Red) 5. Hit OK PS: If you are using XL2007 Goto Home tab>Styles>Conditional Formatting>Manage rules>New...

importing columns
How can all the values from one column without putting the individual lookup in it? I want B3:B53 to be the same on 2 different worksheets, and only have to enter the data once. one way is to put =source!b3 in a cell on the destination sheet and copy down -- Don Guillett SalesAid Software donaldb@281.com "ANTiSEEN" <lakepir8@tampabay.rr.kom> wrote in message news:pBMEe.15413$iG6.9509@tornado.tampabay.rr.com... > How can all the values from one column without putting the individual lookup > in it? > > I want B3:B53 to be the same on 2 different worksheets, a...

Possible to "rotate" range of cells so columns are rows and vice versa?
Is it possible to select a rectangular range of cells and then generate a copied range of cells that is a rotation of the first range, where the columns are the rows, and vice versa? Maybe copy, then paste special and select transpose Regards, Peo Sjoblom davidmichaelkarr@gmail.com wrote: > Is it possible to select a rectangular range of cells and then generate > a copied range of cells that is a rotation of the first range, where > the columns are the rows, and vice versa? > Never mind, I discovered "Paste Special" and "Transpose". ...

transpose 3d cells to a column in single workbook
I would like to extract 3d cells from a set of worksheets into a single column on another worksheet within the same workbook. How can I do this? Insert a worksheet named Summary, then select the 3D range, and run the macro below. HTH, Bernie MS Excel MVP Sub TryNow() Dim mySht As Worksheet Dim dataSht As Worksheet Dim myRange As Range Dim myCell As Range Set dataSht = Worksheets("Summary") Set myRange = Selection For Each mySht In ActiveWindow.SelectedSheets For Each myCell In mySht.Range(myRange.Address) dataSht.Range("A65536").End(xlUp)(2).Value = _ myCell.Value N...

How can I hide the main window / dialog
Hi, I use MFC to paint a picture(.bmp file) on screen. But I want the application main window not to show on screen. How can I do that? I tried to use dialog and doc/view mode, the best I can do is to call a SendMessage(WM_CLOSE,0,0) on OnInitDialog(). But there is still a flash screen of showing the main dialog then close. How can I prevent it from showing? Thanks. "Victor Lai" <VictorLai@discussions.microsoft.com> wrote in message news:0C00076F-B3D0-4EA0-8C6A-09425ADF3D0A@microsoft.com... > Hi, > I use MFC to paint a picture(.bmp file) on screen. But I want th...

VBA From Excel
I wanted to automate running a combined group report set in GP 10. I wanted to automate the process using vba in excel. What is the best way to access to GP object model in excel vba? There aren't too many options for this, even with the GP object model. 1) Use Continuum Integration Library to run the reports with pass-through Dexterity Sanscript. Set CompilerApp = CreateObject("Dynamics.Application") CompilerCmd = "" CompilerCmd = "run report report_name with restriction...;" ... ... CompilerError = CompilerApp.ExecuteSanscript(CompilerCmd, CompilerMess...