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 RepliesI 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.
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 am trying to check a user input to see if the user has entered the date correctly. I am using the code below but it keeps giving me a "Type Mismatch" error.
View 2 Replies View RelatedSearching 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 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
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() ...
I have an imported report in a spreadsheet. It imports to three columns. I need to check each row in column A for three seperate criteria and delete the rows I don't need. I need to delete blank rows and check next row for page header info. Delete these and next rows to next blank cell. Check next row for page header and not delete if not page header. Several rows down will be a cell with 23 blank spaces before the word Reg: and sometimes other words past this but always this first. This row is to be kept. I looked at the FAQ's example of Deleting but I don't think it will work. I also need to put a key word in column A at a point where I want to stop. This report is a couple thousand rows long so a VBA procedure would really save time. I have a procedure I use to check for two zero's in two cells that hide these rows but I couldn't modify it to work on this report.
View 9 Replies View RelatedI 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.
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
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.
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 RelatedI 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 Relatedi 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
My worksheet has a report name in cell A1, a brand name in B2, price name in C2, and color in D2. Row 3 has a table header in cell range A3:E3 followed by rows of table contents at the end of which is a row of empty cells. This sequence repeats itself for a new product: a row with the report name in column A, followed by a row with a new brand name, price and color in the same columns as before followed by the same table header and table contents. The number of products (with the previously mentioned details and table) vary so I would like to write a macro that is not dependent on a set number of products. Each table varies in the number of rows but never contains empty cells and always ends with a row of empty cells. I want to delete all rows with the report name and have all products in one table with the brand, price and color added to the table header and contents.
View 2 Replies View RelatedWhat I am looking to do is search using a user input and select all the rows that contain the entered search text.
I have 4 sheet workbook, columns are the same across them all, and what i require is to be able to take a text input from a user, maybe what column to search, and in what sheet. Then search the relevant sheet for matches. Once found the record that contains the match is to be copied to another sheet where filters can e applied. There will be multiple records selected for each search.
What i want to do is copy all records from whatever date i enter, onto sheet test. The full excel file has over 80 worksheets for each individual rep, the example i attached has 8 sheets..
View 9 Replies View RelatedI'm trying to create a macro that allows users to hide or unhide selected rows, either via toggle or userform. The macro I have looks like this, but for some reason it doesn't hide the rows:
Sub Hide_Range()
Dim UserRange As Range
DefaultRange = Selection.Address
Set UserRange = Application.InputBox _
(Prompt:="Select Range to Hide:", _
Title:="Hide Range", _
Default:=DefaultRange, _
Type:=8)
Rows.Select
Selection.EntireRow.Hidden = True
End Sub
what's wrong with my code and how I can further develop it to let the user decide whether to hide or unhide the selected rows?
i'm trying to form a macro that allows the user to enter a date and have excel auto filter for all dates prior to (or ON and prior to if it makes it easier) that date, and delete all rows with that auto filter criteria in place. i'm working with a old auto filter macro i put together, but cant seem to get the criteria to work with, say, <7/14/2007 for example.
Attribute VB_Name = "modDeleteVal"
Sub DeleteRowByValue()
Attribute DeleteRowByValue.VB_Description = "Macro recorded 7/9/2007 by KHenzel"
Attribute DeleteRowByValue.VB_ProcData.VB_Invoke_Func = "V
14"
'
' DeleteRowByValue Macro
' Macro recorded 7/9/2007 by KHenzel
'
' Keyboard Shortcut: Ctrl+Shift+V
'
Dim rTable As Range
Dim lCol As Long
Dim vCriteria
On Error Resume Next
'Determine the table range
With Selection
If .Cells.Count > 1 Then
Set rTable = Selection
Else...
I need some help with some VBA to hide or delete rows that numerically equate to zero (please see attached spreadsheet template). The cells in the first two columns have text to represent categories, but all the remaining cells are numeric. If any of these are all zero I want to delete or hide the row(s) so the worksheet looks better, with less redundant info.
View 4 Replies View RelatedI am building a budget spreadsheet and would like the date to automatically populate in column H when data is entered into rows J through AN, or alternately update column H when a sum total is populated in column I
I need to repeat this for rows 4 through 63
I was trying to use the following code, but cannot get it tweaked right.
[Code] .....
i want to lock the excel sheet..so user cant add any data or delete it...only through VBA form????
View 9 Replies View Related1) Allow user input of a number between 1 and 999
2) Search column three and delete any rows that don't match this number.
I have some code that would delete any rows within a certain column, based on predefined criteria and i've tried modifying it to suit my needs.
I am using the formula =IF(ISNUMBER(SEARCH("v",A2)),"OK", "Not OK") to say if cell a2 contains the letter v, then return ok. It would be really helpfull if I could say : if cell a2 contains the letter "v" or the letter "w" or the letter "x", then return ok.
View 2 Replies View RelatedI am using the following piece of code to delete unwanted rows from a worksheet:
Sub DeleteDates()
FinalRow = Cells(65536, 3).End(xlUp).Row
For i = FinalRow To 1 Step -1
If Cells(i, 3).Value Like "*2007*"
Cells(i, 1).EntireRow.Delete
End If
Next i
End Sub
What I am working with is a sheet of about 5000 customer appointments going back to the beginning of 2006. A have peiced together a lot of code to format it exactly as I want and to create a pivot table of what is remaining after the rubbish has been deleted.
However I am finding that I need to keep amending and re-running this bit of code, depending on the date range I want to look at. For example the code above will delete all appointments made in 2007.
My question is: is there a way that I could incorporate an input box, whereby excel asks the user for a start date and an end date and deletes any rows outwith that range?
Some important info: All of the dates are in column C, in the format mmm yyyy
I would only need to narrow down to a month - so for example mar 2008 - jun 2008
I've attached a worksheet with two tabs, "before" and "after".
(a) delete the contents of rows in column "C" which <> 0.
(b) resort the data according to date under column "J" (oldest to newest)
(c) retain the integrity of column "W" which is a running total.
I do not want to hide data. I have too much of it, and I also need to use relative formulas on the sorted data (for example, a running total). I've seen other solutions in this forum for deleting rows etc. but it seems that most of them involve "hiding" information, or using filters which will make it difficult to retain the integrity of forumlas elsewhere on the worksheet.
I have a spreadsheet that I use throughout the day and have to send an email to another person when a customer makes a payment. The number of products they pay for will be different each time and I have the code to copy the right cells into an email and the code to insert the right number of rows for how many things the customer is paying for however as I use it multiple times through the day, I need the code to insert the right number of rows, copy it over to the email then automatically delete the rows it has just inserted so it is back to basics so I can use it again.
The code I have for the inserting the rows (from searching through forums) is:
Sub PRows()
Dim NextRow As Long
Dim NrOfCopies As Long
Dim i As Long
Const NrOfCopiesDefault = 1
Const NrOfCopiesMaximum = 9
[code]....
I have a spreadsheet that allows room for 35 students per period, but instead of having the teacher manually go in and delete the extra data and hide the rows, I want to create a macro that will do so.
In cell B4 the teacher will enter how many students are in their first period class. (cell C4 for second period, cell D4 for third etc...) I want the entering of the number to automatically hide the superfluous rows and delete the data in the second column for those rows. I don't want the rows to be completely deleted because another year they may have more students and need those rows back.
I have attached one of the workbooks that I need to put this macro into. I have created room for 35 students in a given period. So if they have 23 students entered into B4 (period one) I would need rows 30-41 to be hidden, and I would need the formulas in B30:B41, E30:E41, H30:H31, K30:K31 etc....deleted.
Basically the point of the formulas is the teacher will enter the total points possible on that given assignment in cell B6, E6, H6 etc....and it autofills that score down, so the teacher only enters those that missed points instead of entering in the missed ones and the 100 percent ones.
I would need those formulas to be deleted because if the assignment was out of 10 then cell B30-B41 will give students a 10, and then the class average will be computing those scores,but those students don't exist.
But if I can't get the macro to auto delete the formulas, I will just not have those formulas in there, and the teacher can enter all scores.
So i don't have any events that i know of that should trigger my macros to run. But everytime i delete a cell, or hide rows, it seems all my code/macros just auto run. And i had a few times when i hid the rows, the code would start, and then my excel application would crash.
View 4 Replies View RelatedI have a financial dataset which I need to "clean" before manipulating/analysing.
Each row of the data represents a completed transaction and the first step is to delete rows that are done with particular (internal) clients; the client data (the client's name) is in Column D.
Currently, I delete the unwanted data by autofilter and delete (code below), however this means the clients to be deleted are only defined within the code. I would prefer to have a worksheet within the workbook where the user defines the clients by adding or subtracting their name from a list.
I have created a dynamic range for the client list by inserting a Named variable along the lines of "=OFFSET(!$B$3,1,0,COUNTA($B$3:$B$200)-1," which works fine.
However how do I work this list into my code so that it works when new clients are added or deleted?
Current code for deleting unwanted client data:
Sub filterdelete()
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
With Range(Range("D1"), Range("D" & LastRow))
[Code] .....