I found this code for hiding an autofilter arrow for column 2. I would also like to hide the arrows for columns 35 through 50. Does anybody know how to do that? Thanks.
Private Sub Workbook_Open()
'hides all arrows except column 2
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False
For Each c In Range(Cells(1, 1), Cells(1, i))
If c.Column 25 Then
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End If
Next
Application.ScreenUpdating = True
End Sub
I have created this Excel schedule that is attached. The point of the schedule is to organize Jobs and Crew members. I have VBA code in, so when I calculate how long the job should take, the arrows next to the job will automatically extend. (This should all make sense if you take a look at the attachment.) The only problem is that my arrows do not extend the correct distance. In the attached file I have scheduled "James Lorenz" to do a job starting on tuesday, towards the bottom of the page I did my calculation that figures he should be on that job for 9.5 days. I would like the Arrow next to James' job to extend 9.5 days on the calender instead of 9.5 centimeters or whatever it is doing now.
automatically hiding empty rows on my table... though the table has been referenced via string of formulas which I added up on my worksheet.. Some of the rows on my table are empty and I tried a VBA code on automatically deleting the rows but then I realized that if that certain 'referenced' string of that cell is not empty anymore, that would cause me much trouble.. so basically I need a code that would automatically hide the rows that displays a result of "" and unhides when it is not empty.
I have some code which looks for the word "Out" in column "L" and hides the row if it finds it. This is running from row 24 to 160 and takes about a minute to run. I've tried two different solutions, neither really quicker than the other.
I'm trying to hide all columns which have the word "hide" in row 6. I have done a similar thing whereby I hide all rows which have the word "hide" in column 3 using the following
Sub HURows() BeginRow = 9 EndRow = 40 ChkCol = 3 For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "hide" Then Cells(RowCnt, ChkCol).EntireRow.Hidden = True Else Cells(RowCnt, ChkCol).EntireRow.Hidden = False End If Next RowCnt End Sub
Alas changing the number and every Col for Row and vice versa doesn't work!! Really I only need to search colums G to U inclusive. The code must also unhide colums if the values in the cells of row 6 change to anything other than "hide".
I have a piece of code (below) that autofilters a table based on a cell value (B7). I am looking to adapt this code so that the table can be filtered by an additional field in B8. This cell may be blank, in which case I want it to select all possible values (not just uncheck them all).
HTML Code:
Private Sub Worksheet_Change(ByVal Target As Range) If (Intersect(Target, Range("B7")) Is Nothing) _[code]....
I am encounting problems with an AutoFilter which is run in a macro after the user has selected dates from 2 Date Pickers (DTPicker) on a user form. The filter is applied to 1 column in a spreadsheet containing dates in the formay "dd/mm/yy" and is supposed to filter dates =2 and between a range of dates as selected. The resultant filter is blank, if the macro is debugged at this point the date format for the 1st criteria is found to have swopped the "dd" & "mm" around but the 2nd criteria is fine. eg. user selected 01/07/07 and 31/07/07 the resultant filter applied is 07/01/07 and 31/07/07. I have re-formatted dates to Long as per the article on this site @ http://www.ozgrid.com/VBA/autofilter-vba-dates.htm but still get the same result ???
Dim fDate, tDate As Date Dim lfDate, ltDate As Long fDate = frmReportMenu.dtpFromDate.Value tDate = frmReportMenu.dtpToDate.Value fDate = DateSerial(Year(fDate), Month(fDate), Day(fDate)) tDate = DateSerial(Year(tDate), Month(tDate), Day(tDate)) lfDate = fDate ltDate = tDate
I'm trying to create a code that when run hides a selection of columns and defines the minimum and interval value for a chart on the active worksheet. My attempt is assigned to the 'update skills' button at the top of the "GRAPH" worksheet of the attached workbook.
While I try to create a sub - autofilter A column and select the...
- first entry (but not as a unique item, as data may changes weekly) once 1st item filtered - I'd do some function: sum weekly results, and keep only one row with the filtered item (paste special/delete rows)
- than step on next entry and do the same as before
- take these steps till the end of autofilter list, than remove autofilter.
.. with this action, I'd get the sum value of my weekly forecast on each individual items.
I have a macro which (see below) which will autofilter a spreadsheet to only show cases which are older than a 200 days. I'd like to add the ability for the end user to change the filter criteria as required (say from 200 to 300).
Ideally this would be done by having the macro reference cell G22 which is where I would allow the end user to change the filter criteria.
The code contain using Autofilter to some columns, & then copy paste to another sheet to first blank row, & sorting it by asscending in Column A. Both sheets are in the same workbook.
But for some reason, the Macro will giving the result expected if i run it step by step (by using F8 button under window VB editor). And if i call the Macro name it stoped in the middle of the process.
What could make this line of code stop working. It has worked for weeks, and just yesterday it stopped working. :x
ActiveWorkbook.Sheets("Email").UsedRange.AutoFilter Field:=2 The sheet is unprotected, and events are enabled. I don't know of anything different than before. What do I look for that would not allow this code to run?
I have some code for applying various filters, up to a maximum of five. However, not all five fields need necessarily have any filter but I can't find a way in the vaConditions line to show 'no filter' or 'All'.
I'm trying to program a macro that filters out codes in an autofilter list. There are about 40 codes in total, however I only want excel to display 3. The current script I am using is below. I know excel lets you filter for 2 criterias in this format, however is 3 or more too much? What would be the best way around this problem?
I have created a macro some time ago that is an integrated part of an XLA. The Xla has worked fine but now, for some reason, the macro fails to import the specified text, it doesn't fail but nothing gets imported. I have tried solving this myself, but alas I am not bright enough
The code is:
Sub GetWorksheet() Dim filetoopen As String Dim wb As Workbook
filetoopen = Application _ . GetOpenFilename("XL Files (*.xls), *.xls") On Error Resume Next
I have a protected worksheet with some macros running on it, and I have been requested to make the workbook shared. Trying to implement this, I keep getting the standard "1004 You cannot use this command on a protected sheet" error when applying an autofilter.
Here is what is making this problem a little tricky:
- As the workbook is shared, I cannot do Protect UserInterfaceOnly because you can't change the protection settings on a shared workbook without unsharing it. Needless to say, I cannot unshare the workbook.
- When I protect the sheet, I do allow the user to use autofilter. Consequently, Sheet.Protection.AllowFiltering and Sheet.EnableAutofilter both are on. Still, I keep getting the error.
where shData is the codename of the sheet, lngField is the number of the field and strArg is the filtering criteria.
My guess is, that the error comes from the fact that (for some silly reason) in VBA the .AutoFilter method is used to create a new filter, not only to use an existing one. The members of Sheet.AutoFilter.Filters collection are read-only, and cannot be used to modify the existing filter.
I do a weekly report for my employer. We have our tabs conditionally formatted. We then insert the corresponding arrows to show a trend to the weekly variance. After the week is up I will delete a column and add a column to the end (making it a trended view) and move the arrows by selecting and dragging all of them into its respected week. I then go on to delete several arrows throughout the worksheet. For some reason my file continues to grow in size however I am only keeping a 12 week trend. The arrows are not deleting properly and are sitting in the background, where I cannot see them. I know this bc I did "find & Select" and chose selection pane. I was wondering how I can resolve this problem or maybe delete just certain columns of arrows (objects)? I had 61,000 arrows and shouldn't have more than a couple 1,000.
I need to analyse trends between a range of cells. The idea is to display an image of a triangle pointing up if the percentage between the current month and the previous is bigger, a triangle pointing down if the percentage has decreased or a rectangle if both months are the same. I've tried to build a userform to ask the user to input the range and then analyze the data...with no luck at all.
Morning all. I'm trying to use VBA to generate autoshape arrows to track changes in values.
Something allong the lines of Column B (Jans data) C (Febs Data) E (autoshape arrow, green up, for improvement, or red downwards for a worsening).
The problem I'm having is postioning my autoshapes. The only way I can do it at present is to keep all columns and rows a standard width, and position using multiples of those.
Is there anyway to set the autoshape to the cell height and width, and position it within the boundries of the cell? This way I don't have to worry about rewiting my code every time I change a cell width?
From the Excel Toolbar - Data/Validation - I'm successfully using a validation list [drop down arrow] to be able to choose from a list and have whatever value I choose entered into the cell. So far so good. Everything working fine.
Here's the thing though, the list I'm referencing is a column with 50 rows. Sometimes there are many items in the column - up to 50, and sometimes only 3 items depending on other variables in the spreadsheet.
Because the list is referencing the entire 50 rows in the column (only way I know how to do it), during times when the column has only 3 items (3 rows), the drop down still shows a bunch of blank spaces (like 47 of them - tons of white space with a scroll bar window that runs down beyond where one can see), which is awkward (strange looking).
How do I program the validation list to only show as many cells within a range that have actual values within the cell, or where <> "" ? Or, in other words, to leave all blank spaces found in the list = NOT SHOWN.
I have a table A-AP 4-499 (row 4 being the column headers and 5 being the first row of data).
I'd like to remove some of the filter drop-down arrows as they aren't needed for some column headers and they also look unsightly.
This code works to remove all of the arrows:
Sub HideArrows() Dim c As Range Dim i As Integer i = Cells(1, 1).End(xlToRight).Column Application.ScreenUpdating = False For Each c In Range(Cells(1, 1), Cells(1, i)) c.AutoFilter Field:=c.Column, visibledropdown:=False Next Application.ScreenUpdating = True End Sub
However, I want to only remove arrows in the following column headers: A, G, I, J, K, M, N, O, P. I want to keep the rest.
I am trying to make different colour arrows appear based on a set criteria, I am having issues with the hide/show amber part, it doesn't seem to be doing the "AND" part.
'Hide/Shows Green' Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1").Value >= 0 Then ActiveSheet.Shapes("Arrow1").Visible = False Else ActiveSheet.Shapes("Arrow1").Visible = True End If 'Hide/show Red' If Range("A1").Value = 0 And Range("A1").Value
