Hide Columns Starting From Active Cell
Apr 30, 2008
Continuing with a large Macro I am trying to peice together, I have reached a hurdle where I need to hide all columns to the left of the ActiveCell (which varies week to week) back to column B.
I have the following code which selects the correct number of cells to the left of the active cell but doesn't highlight the whole columns - just 3 cells for each column (very odd)
ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, "B" & Selection.Column).Select
I'm sure it must be something to do with the
Selection.Column
part as when I write
Selection.Row
it just does 1 cell for each column (as to be expected)
I should mention I already have the coding to hide the columns, just need a way to highlight the relevant ones.
View 3 Replies
ADVERTISEMENT
Sep 17, 2012
I would like to hide/Unhide last active four columns by clicking a command button.
Example:
Columns a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z
When ever i click on command button for the first time, last four columns should be hidden, (w,x,y,z)
On next click, columns (s,t,u,v) should be hidden.
I want similar functionality to unhide the columns with different command button.
View 5 Replies
View Related
Feb 19, 2010
I need to accomplish selecting a range using variables for the starting cell and ending cell rows and columns.
Pseudo code (how I'd like the syntax to be, but isn't):
Worksheets(mysheet).range(startCellRow, startCellCol : endCellRow,endCellCol).Select
With Selection
...
End With
View 6 Replies
View Related
Dec 10, 2013
I have on Sheet1 a table with a list of values in column A, and their corresponding picture in column B. On Sheet2 I have used the define name option to set up an index,match against the original table so that the relevant picture shows whenever someone selects one of the values in column A.
The problem is, while the pictures will change when the values are changed, because all of the pictures are "named", they will continue to show if a value not in the lookup table is selected.
Is there a piece of code that would allow me to specify if cell=blank,hide picture if not show picture?
I have seen the attached code, however this either shows or hides ALL the pictures on the active sheet, while I want to show or hide the picture in the active cell only.
View 1 Replies
View Related
Feb 6, 2008
I have various lists of data (using Data > List > Create List...) that do not always have the first cell in the list in column A. I have spent quite a bit of time searching the internet to try and find a macro that will work the same as... Data > Sort > Sort By: Col xx > Then by: Col xy.
Dave supplied me with a macro that will sort a range by the first column:
Sub SortThis()
With ActiveCell. CurrentRegion
.Sort Key1:=. Cells(1, 1), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub
I have recorded macro that does the same and looked at the code but this uses Range and specifies the cells to be sorted. I have not been able to work out how to modify the code Dave posted to do a minor sort by the second column. I am after a macro that will determine the range (could be a varying number of columns and rows in different places). For however many columns there may be the macro will sort the full width and height by the first column and then the second column, with all the data within a particular row not being split.
View 2 Replies
View Related
Mar 19, 2008
Im writing a macro and have a cell activated (using ActiveCell). If I want to select this cell (the activated one - i.e., k3) along with the 2 cells next to it (i.e., l3, and m3), how would i go about doing that?
I'm wanting to shift 3 cells downward and I know how to shift them down, but dont know how to select the cells i want.
View 5 Replies
View Related
Jul 7, 2014
I am making a spreadsheet in which I have been asked to have certain column greyed-out based on an answer that was given in a dropdown menu. Specifically, a dropdown menu in column D asks if the client was a Youth or an Adult. Depending on what the anwer is, other columns in the spreadsheet will be greyed-out; different ones for each answer. My problem is, I need this to occur for the active row being editted. For example: The first row that the user can input data into is row 4. If D4 is answered with "Adult" then certain other columns are greyed-out. However, if the next time the user updates the spreadsheet with information for a different client (now entering information in row 5) and they select "Youth" for column D5, then different columns get greyed-out. The columns that are greyed-out will depend upon the answer for the dropdown menu in column D for whatever row is actively being editted.
View 5 Replies
View Related
Feb 27, 2009
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 Related
Mar 28, 2008
I need a macro to go through and look at each column between and including E to AW. If it sees a 0 in the cell in row 1 then I want it to hide that column completely and go to the next column.
View 8 Replies
View Related
Jun 20, 2008
Im wondering is ther any possibility to filter colums. My sheet is having column headings as 1's and 0's .Columns with '0' as heading are to be hidden ..can i add any filter.
View 2 Replies
View Related
Dec 27, 2013
I'm looking for VBA code that will:
Hide a sheet whenever it is not the active sheet
View 3 Replies
View Related
Feb 21, 2010
worksheet I am working on at the moment, basically if row 5 has a 0 displayed I want that column to hide, but if row 5 has text of any value displayed I want it to unhide, the range is E5 to BA5 across.
I have draft VBA code as follows:
View 9 Replies
View Related
Mar 12, 2012
I got an excel, with a "validation list" in a certain cell.
Users can select value1, value2 or value3. based on this value, some columns will have to unhide (standard= hidden)
(only for value2 this is the case).
I used the worksheet_change event to determin the value, but so far I'm only getting it to hide...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If C24 = "value2" Then
Columns("H:O").Hidden = False
Else
Columns("H:O").Hidden = True
End If
End Sub
View 5 Replies
View Related
Mar 4, 2013
I need to write a macro that will hide and unhide columns based on a cells value. I know how to write it to hide and unhide rows, but i can figure it out for columns.
For the rows I am using the following:
Sub HideRowsSavings()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
With Sheets("Savings #4")
LR = .Range("A" & Rows.Count).End(xlUp).Row
[Code] ......
View 1 Replies
View Related
Apr 3, 2008
I'm sure this is an easy one, but I have never done this before. I need to know the best way to hide (for example) Columns U:W if cell U3 is blank.
View 9 Replies
View Related
Nov 15, 2006
I need to hide columns in a spreadsheet without using the hide/unhide columns option. I found an article which suggested setting up a combobox from the forms toolbar, this is populated by an input range and linked to a cell for a numeric value. The article although using a macro to hide rows suggested this code
Private Sub ComboBox1_Change()
If ComboBox1.Value = "A" Then
ActiveSheet.Range("1:3").EntireRow.Hidden = True
ActiveSheet.Range("4:6").EntireRow.Hidden = False
ElseIf ComboBox1.Value = "B" Then
ActiveSheet.Range("1:3").EntireRow.Hidden = False
ActiveSheet.Range("4:6").EntireRow.Hidden = True
End If
End Sub
I want to amend this code so that when the number in my linked cell changes the various columns are hidden.
For example if the cell link number = 1 hide columns e to az if the cell link number = 2 hide columns d and f to az etc.
I have had varying success with amending this code to entirecolumn.Hidden and changing the Combobox1. to dropdown1. but am unable to get it to work, I also stupidly deleted the code that was partially working and am now unable to remember what I'd done.
View 9 Replies
View Related
Jan 19, 2007
I have a row in a table from columns C to CZ. All the cells in the row contain a formula, between 1 and 204 cells in the row will have a value (i.e. will not be blank).
The cells with a value will start at column C and may or may not have a blank cell before no more values and blank cells to the end (Col GZ).
Example:
C D E F G H I J K >..........................GZ
23 34 67 74 2 34 6 2 56 all blank ("") to end
or:
C D E F G H I J K >..........................GZ
23 34 67 "" "" 34 6 all blank ("") to end
I need to hide the entire columns when the cells in this row are blank but NOT if the blank cell has valued cells after it (i.e. do not hide columns F and G in the second example.
I can do this by looping back from col GZ and hiding the columns one at a time, which is very slow. I am stuck on the code to select all the relavent columns and hide together.
View 4 Replies
View Related
Sep 4, 2007
I've attached one speadsheet :- "VBA.xls". The following conditions are to be done:-
1. When D2=1, Column "F","G" are visible & Column "I","J","L","M" are hidden.
2. When D2=2, Column "I","J" are visible & Column "F","G","L","M" are hidden.
3. When D2=3, Column "L","M" are visible & column "F","G","I","J" are hidden.
View 9 Replies
View Related
Feb 12, 2013
I want formula which can automatically populate the amount based on the number of months starting from the date vehicle is received. For Eg.
Nissan patrol Rent is AED 5800/- per month & we deliver it on month Sept 2011 for 24 months then i want excel to automatically populate AED 5800/- for next 24 months starting from Sept 2011.
View 5 Replies
View Related
Dec 25, 2013
With first code user selects a sheet from drop down and that sheet is activated. (This works fine)
Now hide all other sheets is what I'm trying to do with the second code. (This IS NOT working)
I assume I can either put the second code in the change event code or call it from the change event code once it works.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$A$1" Or Target.Cells.Count > 1 Then Exit Sub
[Code].....
View 2 Replies
View Related
Jan 25, 2010
This is what I would like to do if possible: If E9 on Sheet1 is 1, then hide columns F-L on Sheet2.
View 4 Replies
View Related
Jul 13, 2009
I am looking or a code, (Or formula if one exists) to hide or un-hide columns depending on what is entered into a cell. I have attached an example to help clarify what i mean. Basically i want:
If A2 is empty, hide columns C, D, E & F
If A2 = Apple, Unhide columns C & D, but keep E & F hidden
If A2 then becomes Banana, rehide C & D and unhide D&F
View 2 Replies
View Related
Dec 7, 2011
I want to hide and unhide columns based on a cell's value.
If D6 of the ‘Summary’ Worksheet Is <> to ‘Test1’ and <> ‘Test2’
THEN Hide columns D:K of the ‘Charts – Source Data’ Worksheet
OTHERWISE Unhide columns D:K
View 4 Replies
View Related
Dec 4, 2013
I am trying to hide/unhide columns based on what is return to cell C2 from this lookup (=VLOOKUP(B2,GM!E2:M129,9,FALSE)). It can only return 3,6 or 9
If it returns 3 I want to hide columns I:P
If it returns 12 I want to hide columns E:L
If it returns 6 I want to hide E:H and M:P
I have been trying to work with this code which I found on this forum but I haven't been able to get it working fully. It only works when C2 is entered rather than calculated and I can't get it working for the M:P part of the third option.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range
Set Changed = Range("C2")
If Not Intersect(Target, Changed) Is Nothing Then
Range("A:Z").EntireColumn.Hidden = False
[Code]...
View 1 Replies
View Related
Jul 17, 2007
If I have a value in A1 and run a macro [button], I want to have it hide a number of rows.
Each number is one column.
eg if A1 = 2 then B:C are visible, D:IV Hidden
if A1 = 3 then B:D are visible, E:IV Hidden
if A1 = 4 then B:E are visible, F:IV Hidden
up to 200 columns.
I tried it as a select case, but it is limited to just 22 cases which obviously not enough.
View 9 Replies
View Related
Jul 18, 2014
I recon, IF Excel is hidden than Windows doesnt see it with ALT Tabs as program and doesnt put it as program in the taskbar.
Is it possible to set a form active as a program? While hide Excel?
View 3 Replies
View Related
Jun 4, 2014
I have a workbook with two sheets, Project Overview and Projects Detailed. In Project Overview column K is different project numbers and what i need the code to do is when this number is pressed it has to go to Projects Detailed and search for this number in column AV and hide the lines that does not correspond with the number that has been double clicked.
In project overview you should only be able to press the numbers from row 9 and down and in Projects Detailed the numbers in column AV also starts in row 9. Some of the cells in column AV are empty and these should also be hidden.
View 2 Replies
View Related
Sep 17, 2008
I have written a VBA procedure which is supposed to hide unneeded columns (based on the a number entered in a certain cell. This is working so far. Since I need to sum only the visible cells in a row i need to clear the contents of a range of cells if they will be hidden. Unfortunately the code i have written runs only to the ClearContents and then starts over from the beginning. If i take out the .clearcontents and put .Select for example instead, the code runs perfectly.
Here is the
Sub worksheet_calculate() 'Hide columns that are not needed
Sheet1. Unprotect
Application. ScreenUpdating = False
Dim i As Integer
Dim r As Integer
Dim s As Integer
Dim rngRange As Range
i = 2
View 8 Replies
View Related
Oct 1, 2008
i have created a spreadsheet to simplify our work flow, I am stuck on what is probably the easiest of the commands.
basically have rows dedicated to specific codes and the colums represent values relating to each code, all codes have a different set of values, the attached example only has a few variables but the actual worksheet will have several hundred.
the idea is the user will input the code they wish to get details on in A2 and then press the command button and it will then show (as per the after sheet in the attachment) just the relevant information for that code, so filter the code in column A and hide the columns which hold no value.
where i am getting stuck is I am not sure the best way to proceed, is it best to create the macro button to do the filter and hide or is there a better way using vlookup and a pop up window asking for the relevantcode to be inputted to to retrive the information, again understand there will be hundreds of colums and hundreds of rows and the values may be 20 or 30 colums apart for some of the Codes so this simplification is really saving the user a lot of time.
View 7 Replies
View Related
Jan 10, 2009
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 13 Then Exit Sub
If InStr(Target.Value, "Other (specify in next column)") Then
Columns("N").Hidden = False
ElseIf WorksheetFunction. CountIf(Columns("M"), "Other (specify in next column)") = 0 Then
Columns("N").Hidden = True
End If
End Sub
but I have a lot of columns that I need to perform as above and I have put the code together as below
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 13 And InStr(Target.Value, "Other (specify in next column)") Then
Columns("N").Hidden = False
ElseIf WorksheetFunction.CountIf(Columns("M"), "Other (specify in next column)") = 0 Then.................
Using the above code, when I selected more than one cell anywhere in my workbook and pressed delete I was bugging out with a runtime error 13 message. You can see from the above code that I inserted "On Error Resume Next" - this got rid of the runtime error 13 message, but now when I select more than one cell and press delete, hidden columns are incorrectly revealed in my worksheet. how I can extend the working code at the top of this posting so that it works for a number of different columns in my Worksheet i.e. without the runtime error 13 occurring and without columns being incorrectly revealed.
View 8 Replies
View Related
