Hide Rows Meeting Date Criteria

Apr 4, 2008

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........................

View 3 Replies


ADVERTISEMENT

Hide Rows Meeting Criteria

Sep 3, 2007

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.

View 2 Replies View Related

Hide Rows On All Worksheets Meeting Criteria

Aug 7, 2007

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

View 6 Replies View Related

Delete / Hide Rows Meeting User Entered Date

Feb 26, 2008

I would like to setup a macro that brings up a dialog box asking for a date in a specific column. Once that date is entered in the dialog box, the macro would then find all rows before that date (in that specfic column) and delete them.

View 9 Replies View Related

Hide/Unhide Rows Meeting Condition In Column

Aug 22, 2008

In the included sheet I have 5 groups of data with five categories in column A. Current, Plan, Plan Var, Prior, Prior Var. I have included the button “Show Options” that opens the userform I created and gives 5 options. What I want is the user to be able to select any number of these options and then upon “ok” the rows in the sheet that weren’t selected are automatically hidden. If the procedure is completed again and a differen set of options is selected I want it to unhide any hidden ones that were selected and hide any that werent selected.

So if just current is selected the sheet will show 5 rows of current and nothing else. If current and prior are selected it will show current prior current prior current prior... etc.

I have some hide code that I created in the file as well.

Sub NotCurrentHide() ...

View 5 Replies View Related

Sum Of Week-to-date If Meeting Criteria

Nov 6, 2009

On the attached file, I would like the values from the Transactions worksheet to show up in the appropriate cells in the XYZ Expenses worksheet. The catch is, I would like the XYZ Expenses worksheet to only show the values from the past week. Past week could be literally, but I'm guessing that might not be possible and it would have to use the past week from the most recent transaction date - which would be fine. So if it worked right, XYZ Expenses > F7 would show -$80, which would be from Transactions H3+H6 (and not -$130, from Transactions H3+H6+H9).

View 3 Replies View Related

Remove Rows Not Meeting Criteria

Nov 30, 2006

I am trying to delete rows - based on 1 field in each row not being equal to ANY of the values stored in an Array. e.g. my array has 3 values, CAT,DOG,MOUSE

cells are:
A1 = CAT
A2 = HORSE
A3 = DOG
A4 = DOG
A5 = BIRD

i want to delete rows not containing CAT,DOG,MOUSE. I think the autofilter could be the trick but cant quite get my head around it.

View 5 Replies View Related

List Values Meeting Date Criteria

Sep 30, 2007

I cannot get my head around this loop, it has been a while since I did anything in VBA and am stuck. I have a list of trades on one sheet, would like to create a column that lists open trades as of that calendar day. Using Sheet(balance) column A as date range. What I am trying to do is test for condition if opening day is less than calendar day AND closing day is more than calendar day...then that trade is effectively open as of that calendar day...so would like to copy it with that calendar day in front of it.

Loop through sheet x and copy all that meet above criteria. Next calendar day, loop through all trades on sheet(x) again and copy etc...here is what I have so far, but I am afraid it is useless...

View 9 Replies View Related

Total Data Meeting Date Criteria

Feb 18, 2008

I've inherited a reporting process which I need to convert to Excel but my knowledge of excel functions is fairly limited. I've attached a sample worksheet which sets out exactly what I'm trying to achieve in the comments. However to summarize here I need to add the data which meets set criteria together and display the result on a different worksheet. The problem I'm having is that the "criteria" the data needs to meet is dependent on info from other cells.

For example I only the totals of the data in column D where the date in column A is 01/01/08 and the data in column E is 1 and the result is to be displayed on a separate worksheet if that makes sense. It's difficult to describe without seeing the example workbook.

View 3 Replies View Related

Copy Rows Into Appropriate Worksheets Meeting Criteria

Mar 12, 2009

I have a worksheet that contains 2 Rn managers per 3 Site and 2 TSR managers per 3 Site.
I want to be able to sort these managers by Site into 6 tabs: 3 RN tabs and 3 TSR tabs.
I want to keep the original worksheet as is and copy the appropriate rows to the designated worksheets labeled SACRN SACTSR VALRN VALTSR and so on. The data range is A:M

The columns of value is A (JOB) and B (SITE) and here is the sample of the data

JOB SITE TEAM
RN SITE SAC GASTON SUSAN TEAM SAC BERNAL
RN SITE SAC GASTON SUSAN TEAM SAC CASTILLO
RN SITE SAC GASTON SUSAN TEAM SAC CASTILLO
RN SITE VAL RANADA TERESA TEAM VAL RINCON
RN SITE VAL RANADA TERESA TEAM VAL RINCON
RN SITE VAL RANADA TERESA TEAM VAL RINCON
RN SITE SAC MCINNIS JOHN TEAM SAC SMITH
RN SITE SAC MCINNIS JOHN TEAM SAC LEMON
RN SITE SAC MCINNIS JOHN TEAM SAC JACK
TSR SITE SAC GRECO DAVE M TEAM SAC KEN
TSR SITE SAC GRECO DAVE M TEAM SAC ROGERS
TSR SITE SAC GRECO DAVE M TEAM SAC LOPEZ
TSR SITE SAC SHAVER KELLY TEAM SAC JONES
TSR SITE VAL FRANCIS TEAM SAC YANG

View 9 Replies View Related

Delete Rows Meeting Criteria In One Column

Jun 4, 2009

I have a large number of user inputs which is linked to a data sheet. In this sheet are rows to be kept and rows to be deleted. See attached example...

I need code to delete all other similar rows than the one with a number (1) in the field Complete. There can only be a number (1) in the field Complete in one of the rows.

But if there is no number (1) in the field Complete in any of the rows, no rows should be deleted.

The data sheet is to be transferred to Access afterwards.

View 3 Replies View Related

Delete Rows Meeting Criteria In Different Workbook

Feb 2, 2010

Broken off from:Delete Rows Meeting Criteria

Thats for that, the sheet in the workbook is called Ticket Handling and if i try to change the name in the VBA code it will not accept this.

It Displays the following error:

Compile Error:

Expected: End of Statment

View 8 Replies View Related

Clear Rows Meeting Multiple Criteria

Dec 13, 2006

In my code I am searching a spreadsheet for certain states and deleting rows that have states I want to exclude. Is there a way to do this with a list of states, instead of having to make many for loops?

For i = lastrow To 2 Step -1
Cells(i, 4).Select
If Cells(i, 4).Value = "PA" Then
Rows(i).Select
Selection.ClearContents
End If
On Error Resume Next
Next
For i = lastrow To 2 Step -1
Cells(i, 4).Select
If Cells(i, 4).Value = "TX" Then
Rows(i).Select
Selection.ClearContents
End If
On Error Resume Next
Next

View 5 Replies View Related

Delete Rows Meeting User Criteria

Aug 20, 2007

I need a macro to sort through a large table of data entries and remove all repetitive entries (entires are based on a number in column A with data entries in B through EY) so i need to locate repetive numbers select entire row and paste them in a new location.

But i cant sort the data as it will change the entire order and i need the data intact.
the user will specify which entry number to search for.

I know some sort of search and loop function would work but i just cant seem to make them work.

View 9 Replies View Related

Automatically Unhide Rows Meeting Criteria

Sep 29, 2007

I'm trying to make my spreadsheet to show next hidden row by entering a text or number on the cells in the range of my code. It works from rows 67 to 135 but I canot make it work with an additional ranges on it. if i insert the same instruction 'Private Sub Worksheet_SelectionChange(ByVal Target As Range)' it shows an error that said ambiguos name detected.?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If RenglonAnt > 67 And RenglonAnt < 135 Then
If Range("C" & RenglonAnt).Value <> "" Then
Sheet1.Rows(RenglonAnt + 1).Hidden = False

End If
End If
If Target.Row > 67 And Target.Row < 135 Then
RenglonAnt = Target.Row

Else......................

View 2 Replies View Related

Delete Entire Rows Not Meeting Criteria

Nov 21, 2007

Excel Range A1 - D500. Data located in column C. I would like to delete the rows in this spread sheet that do not contain any of the following 3 prefixes of part numbers from column C: X-SXP, X-SCM and X-SBP. There are many part numbers but only parts with these 3 prefixes are needed. Part numbers are 10 characters long after the prefix. If any additional information is needed, let me know.

I viewed the Delete Rows Meeting Conditions thread, but the input value in that code is for what you want to delete, and I need to delete everything not meeting the data I want to keep, which is about 30 different part numbers. Also, that appeared to only offer one input and not 3.

View 3 Replies View Related

Delete Rows Not Meeting Multiple Criteria

Feb 29, 2008

I have an extremely large CSV file that I am opening via Excel. Can anyone tell me how to delete a row using multiple criteria. For Example, i want to delete the whole row if a certain cell doesnt equal GA SC or NC. I have seen how it can be done using one criteria but not multiple ones.

View 8 Replies View Related

Delete Rows Meeting Criteria In A Column

Aug 11, 2008

I have searched for this topic, and I have read http://www.ozgrid.com/VBA/row-delete-criteria.htm, I don't really understand it though. I think what I want is simple, I want to search column A for cells containing the word " Total" - if any are found I then want to delete the corresponding rows.

View 2 Replies View Related

Clear/Delete Cells Meeting A Date Criteria

Oct 1, 2006

i created a work sheet with the help of the wonderful people on this forum. my boss loved it! one problem they now want it to automaticly take out a date and time of the cells when the date rolls around again.

example:

12/25/05 late 00:20:06 when 12/25/06 rolls around they want the system to automaticly see it and delete it out with the amount of time they were late or sick ect.... or if the sup was out that day and comes in the next day the system will see that the date has past and will up date the info. the other catch is i need it to move everything up one space when it deletes somthing. i want it to read j18 and k18 as one and so through j and k 40 same for l and m n and o ect.... i attached the sheet

View 9 Replies View Related

Sum Range Based On Dates In Another Meeting Date Criteria

Feb 28, 2008

I'm trying to do a sumif based a range of dates but keep getting a "-" for the result.

In the past when using dates I've had to value the dates in order for it to work. I can't seem to figure out the formula this time around - any thoughts? See attached for sample - my 'broken' formula is in cell C29.

View 4 Replies View Related

Count Adjacent Cells Meeting Date Criteria

Jun 23, 2008

I want to count no of "yes" in Range Column B only if corresponding values in column A is less than 10/06/2008

Please see the attached file for refrence

View 5 Replies View Related

Highlight Rows Meeting Multiple Column Criteria

Apr 17, 2008

I have a large file of invoices, and some have been paid. The problem is, sometimes it is not paid against the correct invoice, so I have a lot of credit and debit that should offset each other. I want to go through the sheet and highlight all the rows that has a matching ID and an a 0 value when total amount column was added up to another row. I would like it so that it highlights a different color for every pair of offsetting amount, but the same color highlight is fine too.

View 2 Replies View Related

Copy/Paste Rows Into New Sheet Meeting Criteria

Jun 19, 2008

1) I have a original worhsheet_A which with header rows(1~6) and many columns.
2) In the sheet_A, there are 2 columns (ColumnN & S) which I need to set criteria (value>5).
3) If any row's data which meet the criteria (value >5) with either column N or S, copy all the entired rows to sheet_B following sheet_A sequence.
4) The sheet_B should have the same header row with sheet_A(row1~6).

Btw, is possible create a corn job trigger this macro to run daily 9am to send out the worksheet_B as email, so that the command button can be disregarded.

View 4 Replies View Related

Count Rows Meeting Multiple Criteria Of Multiple Values

Aug 10, 2009

I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.

I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.

I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.

View 2 Replies View Related

Copy Rows Meeting Multiple Criteria On Multiple Columns

Dec 18, 2006

1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run.
2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L
3. In Column D, I want to specify 3 criterias namely A, B and C
4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo
5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet

View 9 Replies View Related

Hide Rows Based On A Criteria

Oct 6, 2009

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.

View 12 Replies View Related

Excel Macro / VBA That Will Hide Rows Based On Multiple Criteria

May 23, 2012

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.

View 8 Replies View Related

Hide/Show Pivot Table Data Based On Date Criteria

Feb 22, 2008

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

View 4 Replies View Related

Hide Rows Based On Date

Jan 2, 2009

I need to write a macro that will hide several rows if a calculated date's month is not within the month of the report.

Example:
If month of date in cell B20 is not the same as the month of date in cell A5
then hide rows 20 thru 30 if it is the same month, display rows 20 thru 30.

I need to do this comparison twice. If month of date in cell B40 is not the same as the month of date in cell A5 then hide rows 40 thru 50 if it is the same month, display rows 40 thru 50. The dates in B20 and B40 are the result of a calculations (Date in cell A5 plus some number of days) if that makes any difference. I don't think it should.

View 3 Replies View Related

Hide Rows If Date Less Than Today

May 15, 2009

I wish to be able to hide rows if the date value in column B is less than "TODAY" i.e. hide old data.

I have tried the following code but it doesn't work: ...

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved