is there a way to format a cell so that when i enter a time like 1 pm the pm does not show? this is for a schedule that will calculate the sum of hours employees have worked so i can't format the cell as text. also, i want to avoid military time (no 13:00).
View 9 RepliesIf I have data in range A1:Z99 I want to write a macro to hide all the rows that have a zero in the A column.
View 9 Replies View RelatedI'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 RelatedI would like to hide the content of a cell so it appears blank to user. I edit excel bike tour (route) cue sheets for our bicycle club and some info is needed for formulas in calculating total distance but doesn't need to be seen by the bicycle rider.
View 5 Replies View RelatedI was wondering if I could pick up the Field number and the criteria from a cell in excel rather than changing the code in VBA.
Range("A1:K1"). AutoFilter Field:=Range("A1"), Criteria1:=Range("B1")
I was trying this code but I got an error message. Does anyone know if I can pick up these information directly from excel.
The invoice itself simply equals cells from a drop down in the columns to the right hand side. (highlight yellow) What im looking to do is if any of the cells in column I equals - - i would like the cells b16:f16 to hide within the same row from the invoice and if there is something in column I that isn't - - i would like them to reappear.
View 6 Replies View RelatedI use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell
Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?
Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.
Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"
I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).
I have a long list that has all the services from our stores. I´m using the formula below to count how many services does each store have that are over 20 days old.
=SUMPRODUCT((G!$A$2:$A$6000=$B4)*(NOT(G!$I$2:$I$6000="Valmis/Odottaa nouto"))*(NOT(G!$I$2:$I$6000="Valmis, toimitettu"))*(NOT(G!$I$2:$I$6000="Finished/WaitingDeli"))*(Päivämääärät!$C$1-G!$C$2:$C$6000>Päivämääärät!$I$3))
Now i´m wondering is it possible to make a macro that would delete all the cells in the range that do not match the criteria of my formula?
With one exception in sheet G cell A2--> has the number of the store. There are over 40 stores so each store has a number 10, 20, 30, etc...
This is the part of the formula that determines witch store it is counting
(G!$A$2:$A$6000=$B4) B4=10 in this case and B5 would be 20 and so on... By modifying that one part i can count old services for all of our stores.
Now I want the macro to delete all services from the list that are not over 20 days old and belong to the store 10 (B4). If it would delete everything that dont match that formulas criteria i could not get a list of old services for our other stores.
What i´m trying to accomplish is get a list of the services that are over 20 days old. I´m now picking them from the list by hand and that takes a really long time. Because of the number of the stores and the size of the range.
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
I'm attempting to update a previously created workbook with VBA.
The last four rows (80:84) need to be hidden if a particular cell's value is equal or greater to 12%. Even if this cell has been merged (P10:Q11) and its contents are a formula (D10-A9)/A9.. would there be a macro to solve this?
I am using VBA to create a word document (.docx). This word document contains plain text content controls as well as picture content controls. I then use VBA to automatically select a picture based on the code below
Code:
Set oCC = Word.ActiveDocument.SelectContentControlsByTitle("TabPic").Item(1)
On Error GoTo TabErrorHandler
oCC.Range.InlineShapes.AddPicture Filename:="X:XFERANDREW-TDCD " & LblVL &
[Code].....
After the document has been closed down I try to open it again and I am told "The file cannot be opened because there are problems with the contents."
When I click details it says "Unspecified error" and "Location: Part: /word/document.xml, Line: 2, Column: 0"
If I click ok it says "Word found unreadable content in "". Do you want to recover the contents of this document? If you turst the source of this document, click Yes.
Clicking Yes opens the document with all the contents and it is now renamed to Document 1. If I click no it does not open.
how to combine the content of 2 cells into one cell and have the information separated by a comma.
For example:
CELL 1:
Software 1
CELL 2:
Spreadsheet Software
The desired results is:
CELL 3:
Software 1, Spreadsheet Software
What am I doing wrong here?
I have a code and it doesn't error out, but it won't hide the rows either. I'm pretty sure the red is what needs to be altered. I've tried adding "Selection.", "Rows." and "Cells." and none of them are working.
I have a data ranging from J:EW with a heading in row 2 e.g. J2 the heading is "OT1 Hrs"; K2 the heading is "OT1Amount"; L2 the heading is "OT2 Hrs" and so forth. My difficulty is, it is too much multiple columns from J to EW, how can I draft the vba to hide a column that has "Hrs" in row 2 heading instead of doing it one by one.
View 3 Replies View Relatedi would like to hide different sheets at one shut. i tried this, but did not work:
Dim sh As sheet
For Each sh(1 3 5 7 9).Visible =xlSheetVeryHidden
Searching through hide/unhide topics seem to yield more complicated codes then my meager needs. Within my worksheet I have a column BD which contains a list of values,
ie:
BD
1
2
3
6
3
2
1 etc..
I've created a toggle button "HideRow", which I would like to use to hide/unide only the rows containing value "2" within column BD.
I have tabs within my workbook that are pulling from other tabs in the same workbook. There are 30-40 tabs, and only 10-15 are used at a time. Is there a way to hide tabs that are not being used? If I do a calculation in A1 and it's greater than 0, I'd like the tab that is pulling from A1 to be visible. If A1 is 0, then I'd like to hide it.
View 14 Replies View RelatedI am trying to hide or unhide columns E:BL depending if the the cell value in row 54 contains a value (Note cells in row 54 contain formulas). I have written the following code but get a runtime error on the else statement.
Sub UpdateCashflow()
Application. ScreenUpdating = False
ActiveSheet. Unprotect Password:="LD"
ActiveSheet.Select
Dim c As Integer
For c = 5 To 64
If Cells(54, c) = "" Then
ActiveSheet.Range(Cells(54, c)).EntireColumn.Hidden = True
Else
ActiveSheet.Range(Cells(54, c)).EntireColumn.Hidden = False
End If
Next c
ActiveSheet.Protect Password:="LD", DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.EnableEvents = True
'
End Sub
I have the following code that works well on a single worksheet
Sub hide()
For Each rw In ActiveSheet.UsedRange.Rows
If rw. Cells(3) = 0 And rw.Cells(5) = 0 And rw.Cells(6) = 0 Then rw.Hidden = True
Next rw
End Sub
However I want it to run on each worksheet in the workbook when I run the macro. I tried this but it seems to loop continuously on the first worksheet. When I press escape the line "next row" is highlighted in yellow by the debug in the VBA Editor.
Sub hide()
Dim ws As Worksheet
Dim rw
For Each ws In ThisWorkbook.Worksheets
For Each rw In ActiveSheet.UsedRange.Rows
If rw.Cells(3) = 0 And rw.Cells(5) = 0 And rw.Cells(6) = 0 Then rw.Hidden = True
Next rw
'Next ws
End Sub
I have a macro that hides rows in my workbook if certain criterias are met. I'd like to add a criteria to the logic that only hides rows if the date in cell 17 is less than 3 months from today's date. The date in cell 17 is shown in this format: 25-03-2008.
Sub ForceHide()
Dim ws As Worksheet
Dim line As Integer
Dim Endline As Long
For Each ws In Worksheets
If ws.Name <> " Total" And ws.Name <> "Batch" And ws.Name <> "Summary" And ws.Name <> "PivotTable" And ws.Name <> "Fields" And ws.Name <> "RTP" Then
With ws........................
I have a very large table and i need to be able to Hide/show specific ranges based on:
Filter +and+ specific cell values in columns
brief example of the table : tablee.png
So...
1. Filter Column "B" (in this case we select "HELPING")
2. Auto hide/show collumns. - IF "C1" = "Required" THAN Show "C:E", IF "C1" ="N/A" , HIDE "C:E" and so on for every column like above.
There are over 80 columns like the "C:E" range. and I only need to show those that are "Required".
I would like to hide rows for data that does not meet specific criteria. For example: If a user selects room number 101 from a drop down in B1, I want to filter data in range A3:F1000 to show me only room 101 rows (A column) where C column contains a value larger than zero OR D column contains a value larger than zero OR E column contains a value larger than zero. I do not want it to return rows where 101 may be in other columns beside A.
Sample:
A B C D E F
__________________________________
1 ROOM: 101
2
3
4 101 XX 1.2 0 0 P
16 101 YA 0 0 1.1 L
23 101 JJ 3.2 2.1 0 L
55 101 JJ 0 0 1 P
So, if a row contains 101 in column A and all three values in columns C, D, and E equal 0, then those rows will be hidden.
I want to write a VBA code, so I can apply dates criterias to my pivot table. Say, I have 1-Dec-2007 in "C2" and 10-Dec-2007 in "C3". Now I want my pivot table to show me the dates between those two dates and the data that goes along with it. I have written this code, but it keep debugging:
Sub FilterDates()
Application. ScreenUpdating = False
Sheets("PnL").Select
Sheets("PnL").PivotTables("PivotTable3").PivotFields("Date") _
.PivotItems("01/01/1950").Visible = True 'to always have 1 populated
I have looked at the below Hide/Show Pivot Table Field Items help web pages:
Hide/Show Pivot Table Field Items
Hide Pivot Table Fields Pivot Items by Criteria
I am trying to use the above, but with dates in the following format in each cell:
YYYYMM
200612
200701
200702
200703
200704
200705
200706
etc, etc
The below code is working for >200702 and removes all years/months prior to this entered value. Unfortunately the code does not work when a user enters <200706, instead the code goes through to the “NonValidCriteria” prompt.
Sub HideByCriteriaYYYYMM()
'Declare variables
'SEE: [url]
'SEE: [url]
Dim pt As PivotTable, pi As PivotItem
Dim lMonth As Long
Dim strCri As String, strCri1 As String, strCri2 As String
Dim bHide As Boolean
Dim xlCalc As XlCalculation
How to determine the correct formula for this requirement?
Assuming I have 2 individual excel files and an index excel file (in reality, there are more than hundreds of file). For index excel, once the user enter part number (eg. 1 or 2), the excel will look for the part number excel file and determine vlookup function.
The only problem I have here is I can not make the index file automatically add the part number shown on most left column into the required formula (replace the part number file section).
I tried use the indirect function but this require each file to be opened, which is not possible for actual use. I am looking for a function that can work in closed worksheet.
Attached files :
index.xlsx‎
1.xlsx‎
2.xlsx‎
I am working on developing QR Codes using some MATLAB code and it would be really convenient if I could create an excel program which changed the background color of a cell containing a 1 to black and a cell containing a 0 to white.
View 2 Replies View RelatedI am currently trying to create a spreadsheet whereby if I enter certain text in a cell in Column A on worksheet 1 that correlates with text in a cell in Column A on Worksheet 2, then the description in Column B in Worksheet 2 is entered into Column B on worksheet 1.
For example, if worksheet 2 has the following:
Column A Column B
XXXX PRODUCT 1
YYYY PRODUCT 2
and I enter XXXX in column A on worksheet 1, I want Column B on worksheet 1 to automatically enter PRODUCT 1.
multiple search match and replace content in a different column so for example
new workbook (look up table)
sku
search1
search2
[Code]....
so something like where you compare two tables and find and replace based on another cell that matches in my sku.. more details would be if the table column aren't exactly matching but the column header and the row header would match and fill or replace in the correct/corresponding cell is there a macro or vba to do this job in excel?
I have the names of companies in one column, and the amount they owe in cells in the column beside them. I then have a second list of companies that is a subset of the first. Is there a formula that would place the amount they owe in teh corresponding cell adjacent to the compny in the second list? I've attached a sample workbook, Full Company List in column A, amount owing in B, trimmed down list in D and ideally I'd like the corresponding values in E.
View 3 Replies View Related