I'm trying to hide all columns in which row 4 = "Test". MyRng is set to "A3:AJ3"
Here's my code that gives an error. I'm guessing it's because I use "column" as a variable in Cells.
I just want to Hide all the blank columns on the sheet apart from column A.
I already have this code, just dont know how to alter it:
VB:
Sub HideEmptyColumns() Dim c As Long
Dim n As Long
Application.ScreenUpdating = False
[Code].....
I was wondering how I might be able to hide columns based on their 1st row labels with vba - I never know where these columns might be on different file I get but I want to hide the same columns every time. right now I am just specifying column numbers or letters but it's not working out:
Columns("A:C").EntireColumn.Hidden = True
Columns("G:I").EntireColumn.Hidden = True
Columns("K:R").EntireColumn.Hidden = True
Columns("T:V").EntireColumn.Hidden = True
ActiveSheet.PrintOut
Columns("A:C").EntireColumn.Hidden = False
Columns("G:I").EntireColumn.Hidden = False
Columns("K:R").EntireColumn.Hidden = False
Columns("T:V").EntireColumn.Hidden = False
with 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. .
=IF(ISERROR(VLOOKUP(70036,PositionDataBuy!$D$2:$U$505,17,FALSE)),"Not Found", VLOOKUP(70036,PositionDataBuy!$D$2:$U$505,17,FALSE))
To remove the error if Vlookup can't find a match - the formula above uses the ISERROR.
If vlookup produces an error (can't find a match) the Excel cell returns "Not Found", otherwise, it returns a valid Amount from column 17.
This formula is inserted into an Excel specific location using Access VBA based on a set returned from a SQL Server Query.
An Excel Worksheet PositionDataBuy is created in sort order of Contract numbers.
The problem came a few days later when a condition of 3 of the same Contract numbers for the same day had different buy Amounts.
So the Vlookup found the first contract number (e.g. 70036) and the value associate with the first instance in PositionDataBuy three times)
The challange is now to use Vlookup for the contract number, then (if it matches) look over at the Amounts column and determines that from each contract record (assuming a duplicate)
it also matches the volume.
So, if Vlookup's error message is False (found first value), another nested if (with the ISERROR) will check the second required column.
But, what if the valid column is on the second record with 70036? I suspect if an error was generated the Vlookup is done and it won't continue.
The data below is fudged, lets assume that no two volumes would be the same for now.
Example: 70036 also needs to be tested against volume and then return the Amount
Contract Volume Amount
70035 5 100
70036 7 120
70036 3 33
70036 6 22
70037 1 11
I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.
A1 represent a number of years and B1 represent gains or losses (negative)in dolars.
The way I see the logic is as follows:
If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)
If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.
On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.
I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:
If A1 = 0 then C1 = NGL
If B1 = 0 then C1 = NGL
If A1 >= 1 and B1 > 0 the C1 = LTG.
If A1 >= 1 and B1 < 0 the C1 = LTL.
If A1 < 1 and B1 > 0 then C1 = STG
If A1 < 1 and B1 < 0 then C1 = STL
I 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......................
This must be a simple fix. But I can't seem to get it. I need to test the data of each cell in column B against each other cell in column B. If the data is equal then move it under the first cell of the test.
1
2
3
2
2
3
4
Group the 2's and 3's in order.
Finally, then create borders around each group of numbers.
I want to create a macro that checks whether a specified range of columns is hidden, and
a) hide them if they are not hidden, or
b) unhide them if they are hidden,
i.e. toggle the Hide state.
What method can I use to query the hidden state of a selected range of columns and return a boolean value?
I am using a count if to look at a column C in a worksheet a and return how many time the word 'on test' occurs. This is then returned to a table in another worksheet. I have 5 worksheets in total with the same columns, how can I do multiple countif - so that it will count all the 'on test' in column C for all 5 sheets.
View 2 Replies View RelatedI have a large spreadsheet converted from pdf whose data still appears in A4 reading format.
I need to move part columns of data from 6 columns to form 1 large column in column A.
For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !
Also need to delete unnecessary 'page headers' throughout as in rows 2-6
I was wondering if it is possible to hide a column(s) but still have data in that column be visible at the bottom of the spreadsheet. For example, if I hide column G can it only hide the column from rows 1 - 50 and then the column will appear after row 50?
View 3 Replies View RelatedI 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 RelatedI've come across a spreadsheet where certain rows and columns (typically top rows or left columns) are hidden; however, there's no way to unhide them (the unhide function is grayed out) and it doesn't seem to be protected or no visible macros/vba on the file.
View 3 Replies View RelatedI have a spreadsheet that has 28 columns for time entries. Typically only the first 12 columns are used, so I would like to hide the remaining 16 columns (which makes the spreadsheet much more user-friendly). It would probably be nearly impossible to teach all of them how to Unhide the remaining columns (and re-Hide), plus I would like to use the full-screen function when employees enter thier times. I would like to use a form control in the column heading so that when the employees 'check' it, it will Unhide and then re-Hide the columns. Any way to do this? Seems like a VB thing to me (out of my league, but would be happy to add one in!).
View 2 Replies View RelatedI have created this macro (below) in a standalone spreadsheet and the expected results are that Columns A,B,C,D,G,H will be displayed after I run the macro.
But when I use the same macro in my production worksheet (columns and ranges adjusted accordingly) this macro creates the following results: Column A is displayed and all the rest are hidden (B,C,D,E,F,G,H). I am stumped as to why this occurs. Can you advice me as to how to get this macro to work and display A,B,C,D,G,H ?
I'm using Excel 2003 and want to hide columns D:E & I:J. I have script as follows which hides I:J but I am unsure how to get it to include the other 2 columns.
View 4 Replies View RelatedI want to create two buttons.. one name HIDE and the other UNHIDE.
What I need hide button to do when i click it is hide the 4 columns to the right (not always going to be columns b:e)
For the unhide button unhide the 4 columns to the right (not always going to be columns b:e)
I once saw an excel sheet where I could hide or unhide a section by some + and - signes above the column-letters.... I have searched for this but I only get the ordinary hide/unhide solutions.
View 4 Replies View RelatedI'm attempting to put code in that will hide 2 individual columns when a particular cell in another column has no value in it. The cell, however, has a formula in it, and I'm not sure if that is my issue.
Essentially, if the formula in A32 returns a value, I need columns M and P to Hide. If it doesn't return a value, I need them to stay put. The value returning in A32 is a number, but it can be treated as text as it is an ID number and is not needed for any calculation.
Here's how I have it set up now:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A32") "" Then
Columns("M","P").EntireColumn.Hidden = True
Else
Columns("M","P").EntireColumn.Hidden = False
End If
End Sub
It's not working - nothing is happening.
Using VBA or by other means, I want to disable user entry in cells G1 to X1 if the user places a value in A1, similarly if the user places a value in A2 then disable user entry in cells G2 to X2, and so on.
View 7 Replies View RelatedI am using drop-down lists in a cell to filter out some data in a spreadsheet. Works great, but I would like to hide certain columns depending on which item is selected in the drop-down list. I have written a macro to do this, but I cannot get the macro to run automatically every time there is a change in the drop-down list.
View 9 Replies View RelatedThe other day I had posted a thread asking if anyone could help me ensure certain columns were hidden when a .xls was opened.
Here-> http://www.mrexcel.com/forum/showthread.php?t=312938
Now Id like to be able to hide the columns only if row B of the colum contains the text "hide me". Please not the cell may contain other text, such as "Q1, hide me"
Anyone know if its possible?
eg I want to hide Cols B and D
1ABCD2Shop items3dateQ1, hide meItemSupplier, hide me401/01/2006q1 2006JuicePepsi505/01/2008q1 2008crispswalkers
My goal is to be able to hide certain columns in the workbook based upon certain criteria. Moreover, I need to be able to hide columns in a certain tab even if I working in a different tab (i.e. if I am in Tab "A", I want to be able to hit "Ctrl + Alt + X" and be able to hide columns in Tab "B").
In order to accomplish this, I selected the Visual Basic icon. I then clicked on the "Insert" drop down menu and selected "Module". I have included a sample of the code that I wrote and inserted in to the module (see below). This general pattern of code repeats itself for 16 different macros - all in this same module.
Sub Macro01()
Worksheets("Input - Historical Financials").Columns("A:AD").Hidden = False
Worksheets("Input - Historical Financials").Columns("D:AD").Hidden = True
End Sub
Sub Macro02()
Worksheets("Input - Historical Financials").Columns("A:AD").Hidden = False
Worksheets("Input - Historical Financials").Columns("F:AD").Hidden = True
End Sub
I have a list of 24 columns (12 months each for forecast and actual data). I want to put VBA code in the worksheet that will hide a column based on a cell value in each column. On line 4 there is a formula that returns either "show" or "no". The columns are AS:BP. The code needs to run on a change in any of the cells AS4:BP4.
View 9 Replies View RelatedCurrently using this code to search Column D and hide if the cells are blank. How would I go about also hiding Column C as well, If D is blank?
I am using the spreadsheet as a grading template. The assignment name is under column C and the grade under column D. If there is no grade then I would like to be able to hide the unused assignment column along with the empty grade column.
Sub HideColumnsInd()
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Dim rCell As Range
For Each rCell In Range("D3:D48")
If rCell = "" Then
rCell.EntireColumn.Hidden = True
Else
rCell.EntireColumn.Hidden = False
End If
Next rCell
Application.EnableEvents = True
End Sub
I'm trying to hide columns M:R on sheet "1up" if cell N4 on sheet "Fill In" is blank and unhide those columns if N4 contains an "X". Columns AK:AP should also hide or unhide on "1up" if AD4 is blank or not on "Fill In".
I've pieced together the below code by recording macros and piecing together code I've found in other threads on here. I placed the code in Sheet 1 (Fill In).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("N4").Valuse = "X" Then
Sheets("1up").Select
Columns("M:R").Select
Range("M7").Activate
Selection.EntireColumn.Hidden = True
End If
Sheets("Fill In").Select
Range("N4").Activate
If Range("N4").Valuse = "" Then
Sheets("1up").Select
Columns("M:R").Select
Range("M7").Activate
Selection.EntireColumn.Hidden = False
End If
Sheets("Fill In").Select
Range("N4").Activate
End Sub
I have a sheet that shows multiple part numbers as the headings for the columns. The rows show each individual job/install. If there is an "x" in the column, then the part is needed and must be pulled by the warehouse. If there are no x's at all in the column, I would like the column hidden so that I can print out a concise "pick list" for the warehouse that only shows those items needed.
View 9 Replies View Related