I need some VBA code to hide columns in spreadsheet when run. Basically, in row 7, I've entered HIDE in the columns that I want to hide when the macro runs (i.e. columns F,J,H,I,K, O,P,R).
View 7 Replieshow to automatically delete (or hide) columns in a spreadsheet given a list of columns that I want to retain (or see).
These are the columns I want to retain (or see):
Name
1st Phone Number
2nd Phone
Country
Conditions
Email Address
Enrollment Status
Room not available
Roommate
Mailing address
Payment Record
Payment Status
Gender
Requested room type
Total Payments to Date
What is your meal preference?
The attached has two tabs......columns I want to retain (or see) and the raw data of all the columns.
Below code isnt working for "5 Metre", columns dont hide from row 24 down.
My A10 is a drop down where i select 5 or 6 or 7 metre. If i select 5 it only hides rows 22 and 23.
I think the 2nd and 3rd lines marked red are stopping the rows from hiding!
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$10" Then
Rows("22:40").Hidden = (Target.Value = "5 Metre")
Rows("24:40").Hidden = (Target.Value = "6 Metre")
Rows("26:40").Hidden = (Target.Value = "7 Metre")
End If
End Sub
VBA code that can highlight all columns except ones highlighted?
View 1 Replies View Relatedwith a simple code that could:
If column K2:k466 is blank or says "No" then columns L & M are hidden. If it says "Yes" it is not hidden.
Also,
If Column N2:N466 is blank or says "No" then Columns O,P,Q,R are hidden. If it says "Yes" they are not hidden.
Finally,
If Column V2:V466 has a zip code (or number) then columns T,U,W,X,Y,Z,AA,AB,AC are present. If there is no value in any of V2:V466 then they are hidden. .
I have a worksheet in excel that I have hidden columns from AA right to the end. Which gives the user a nice worksheet with everything to the right of AA hidden in blue. What I would like to do is now centre the worksheet in the screen and have the same hidden blue effect to the Right of the worksheet in this blue.
View 1 Replies View RelatedI am filtering the data displayed in a chart by hiding columns. I would also like to filter the X-Axis labels by hiding columns. If I do this manually I have no problems but when I run the following macro the chart gives a reference error for the X-axis labels.
Sub ShowA2()
Application. ScreenUpdating = False
num = Sheets.Count
Sheets("X-Axis").Activate
Range(Columns(1), Columns(256)).Select
Selection.EntireColumn.Hidden = False
For a = 1 To 5
Sheets(num - a).Activate
If ActiveSheet.Name = "A2 Data" Then
Columns("A:Q").Select
Range("A10").Activate
Selection.EntireColumn.Hidden = False
Sheets("X-Axis").Activate
Columns("A:E").Select......................
I have a simple spreadsheet to track my mileage. It has a column calculating miles driven since the last fill-up. The "miles driven" formula is simply =B4-B3 (current odometer reading minus previous odometer reading.)
Until I input my current odometer reading, the following row's "miles driven" column displays the previous odometer reading as negative value. Example:
ROW 2: Current Odometer Reading = 53,000
ROW 3: Current Odometer Reading = 53,200; Miles Driven = 200
ROW 4: Current Odometer Reading = blank (awaiting input); Miles Driven displays as -53,200
ROW 5: Current Odometer Reading = blank (awaiting input); Miles Driven = blank
How do I hide that negative value & just display a blank cell?
I know how to keep other types of error values from displaying (zero values, #DIV0!, etc.)how to hide this single pesky negative.
I have a spreadsheet that has a range of a2 thru j62. I want to scan column B and if it is a 0, I want to hide the row and set the print area to the a2 thru j62 range and print the spreadsheet with the '0' columns hidden
View 2 Replies View RelatedI am making a database system that tracks student activity in my schools lab for people taking foreign language courses. I'm trying to make it so students must use the userform to manage they're checking in and out, and not be able to have any direct access to the Spreadsheet itself. Also, it would be nice if the whole Spreadsheet was hidden, invisible, and only the user forms could be seen.
View 4 Replies View RelatedI have a spreadsheet that is linked to another spreadsheet in a workbook. The information comes from an export of an access query into a template in excel that I am using just to store the values, then I link the values to the appropriate field in another sheet. I was wondering is there a way to programmatically hide blank rows in this sheet starting at a specific row of the page.
View 4 Replies View RelatedHave a spreadsheet with 15 columns. In one of the columns is the name of the company and that column is not in alphabetical order. The city, state, zip code, business type and all the other pertinent data about that company is in the same row as the name of the company. My intent would be to put the company names in alphabetical order and keep all the company information in the same row as the company name.
View 1 Replies View RelatedI know this has been kicked around for a long time, but does anyone know of a way where the user cannot see or change the code. Either would be acceptable, but I would prefer where the user can not see the code.
View 2 Replies View Relatedi want to know is there anyway we can hide a row if the value of a particular cell is 0 ?
Like if a1 = 0 then Row A should be hidden?
Have created a userform for people to use to populate a spreadsheet and based upon the values filled in cells, and date segregators, formulae determine whether the line of data is classified as new / increased or reduced.
Obviously I can sort this in excel quite easily by this criteria.
At the moment it is about 500 rows deep,and is sorted by N/I/R.....
What I ideally want to be able to do is create 3 new worksheets, N/I/R and then select all the N's and bung them in sheet N, row 2, and then repeat selecting all I and then all R, and pasting in respective sheets.........
Obviously it can be done, but I am far too rusty to do this.
At the moment, all is sorted in one worksheet, alpha ascending...
Is there any way I can e-mail an Excel spreadsheet through code?
View 9 Replies View RelatedDoes anyone know the code I would use to input a message box into a spreadsheet that says "Do NOT click on this cell" when a user clicks on cell C5? I only want this message to come up if the user click on cell C5.
View 9 Replies View RelatedI am having trouble with my workbook. When I am changing the value in the main cell ("Moses!B5") the whole program freezes. The code is as follows and was developed to ensure the values in my cells did not change as the main cell value changed:
Private Sub Worksheet_Calculate()
For Each c In Range("A1:AF14")
If c.Value = 1 Then c.Value = 1
Next
End Sub
I think the problem is that all of my sheets are calculating and I only want the one which contains the cell which matches my main cell ("Moses!B5"). Is there a way to trigger calculation only for the sheet which contains a date cell matching my main cell?
I am using the code below on my spreadsheet. What is does is hides Row #1 until Row 41 is reached. When 41 is reached Row 1 appears. Unfortunately (for me) I need to alter this code and was wondering if anyone could tell me if it is possible. First the
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Rows(1).Hidden (Target.Row < 41) Then
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Rows(1).Hidden = Target.Row < 41
ActiveSheet.Protect
Application.ScreenUpdating = True
End If
End Sub
What I would now like to happen is that Row 1 stays hidden UNTIL Row 16 is OFF Screen. Can that be done? What has happened is that my row 16 contains the heads for my input table. I have now had to set the rows to resize to accommodate data input. If no rows are resized then Row 16 stays visible until I reach row 40. When row 41 is reached then Row 1 appears which contains my headers as well. I hope this makes sense to someone...
SO, IF I can get a code that would keep row one hidden until Row 16 is off screen then it will not matter what rows expand.
I have a very large spreadsheet that I work in and then print from. When I print, I need to hide about 20 columns which I do manually. I am curious if there is a way I can do this easier. If I could somehow filter columns, or set mutliple columns to a name for a dopr dowm box, or possibly a macro.
View 9 Replies View RelatedI am trying to use an IF formula to do the following for each row in a large spreadsheet: If the value in column W = 0, show columns A,B,C and E. I have one formula I've used that doesn't work, but as posting what I think is the correct answer could get my post deleted, I won't put it here.
View 3 Replies View RelatedI have found vba to hide rows that contain zero values, but not columns.
View 6 Replies View Relatedbelow is some code from a spreadsheet I am working on,,,,,, as you can see it adds items to a dropdown list in a combo box. This may require an update at another time,,,,, is it possible for the list to be updated from a list in the spreadsheet, therfore preventing people from accessing the code
View 4 Replies View Relatedi have a spreadsheet that uses control functions to run vb code. i am trying to make things as user friendly as possible for all of the employees. i am currently working on a control to clean out any rows that the first cell has a value of "N/A" or is left blank. i have the code for the blank cells, but cannot figure out how to add the "N/A" cells.
here is the current
Private Sub CommandButton2_Click()
Dim rng As Range
On Error Resume Next
Set rng = Columns(1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If
End Sub
I have a file which contains many worksheets with many buttons on them for doing administrative work when updating data. I want to be able to click on a button on my "admin" worksheet to hide all the other buttons on the other worksheets. This is hide the buttons from the view of 'readers' so that they will not be tempted to click on a button to see what it might do. I have tried several different variation of coding, but can not seem to get the "right" object to work with so as to set the code to be xxxx.visible=false. I seen references to Shapes, Shape, Control Toolbox buttons objects, CommandButton, etc.
I would really appreciate if someone could give some pointers on how to designate in VB code, which will lend itself of future updates, the following:
in a toogle button, loop through the series of worksheets making each command button on that sheet set visible = false. Then for the other portion of the toogle repeat the process, but set visible = true.
I am new to programming in excel. For example user enters the value 1 in A1 and 2 in B2 The answer should be in 3 in C1. How to write this code ? Also is there any way where i can let the user copy the VALUE from the result (C1) into another Sheet in case if am using + Sign to obtain the result in C1 instead of writing a VBA Code.
Basically i am looking for the user to in put data in A and B and the result should come in C like wise i want the user to input the values in few rows and the user should get the result in the respetive rows under column C. The user should copy the values from C into another work sheet/ work book. The user should not have access to the formula but should be able to input values and copy the result. can i do this without vba code if so how ?
I need a code to hide and unhide 6 Active=X Listboxes on sheet 2.
I need to link this to a Active=X Commandbutton.Can the commandbutton caption say hide/Unhide listboxes when selected.
The below sheet sometimes returns blank (“”) cells in columns C:F. I’m looking for a code that hides the rows 113: to 117 when column C:F (all of them) are empty (“”) for that row. In the above example rows 115:117 should be hidden....
View 9 Replies View RelatedI have a sheet with 3 ranges of data in Sheet4, being partcode (B20:B20000), description (C20:C20000) and price (D20:D20000).
What I'd like is a macro to take a keyword entered in Sheet4!C17, search the whole range (B20:D20000), and hide the rows that don't contain the keyword.
Autofilter would be the logical method to my way of thinking, but can that work with a keyword entered in a cell, and can it all be hidden in a macro behind a control button?
I need to be able to save a copy of my spreadsheet but protect the structure so that the hidden rows cannot be opened by the person that I e-mail it to. At the moment I am using the current
Private Sub CommandButton2_Click()
Do
fName = Application.GetSaveAsFilename
Loop Until fName <> False
ActiveWorkbook.SaveAs Filename:=fName
Call ProtectRobin
End Sub
Private Sub ProtectRobin()
ActiveWorkbook.Protect ("Robin")
End Sub
The saving part of the macro is working perfectly, however I cannot get the protection to work. It must have a password - I do not want the receipient to be able to go 'Tools' > 'Unprotect Sheet'.