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 RepliesI 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'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 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 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 RelatedI have the following codes to delete all blank rows in column A
Dim lastrow As Long
lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastrow
With Sheet1
For t = 1 To lastrow
If Cells(t, 1) = "" Then
Rows(t).Delete
End If
Next t
End With
End Sub
Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.
I have formulas in cells B8:B365 that pull project names (in words) from a plugin using values specified in other cells (non-formula). If a project is inactive, the cell returns a 0.
I am looking for a macro that will hide the rows in this range where the B cells = 0. If there is no data at all, I want to leave the row as-is (unhidden). I also have 2 other specific sheets in the workbook that I'd like to include in the formula without having to repeat the macro 2 other times.
The one I am currently using just hides every row, regardless of if there is a title, a 0, or nothing. Here is the code (I don't know how to create those nifty little text boxes):
I have the following code which works perfectly. It needs to hide rows 3:60 based on the value in K2.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K2")) Is Nothing Then
If Not IsNumeric(Range("K2").Value) Or Range("K2").Value = "" Then
Range("A3:A60").EntireRow.Hidden = True
Else
Range("A3:A60").EntireRow.Hidden = False
End If
End If
End Sub
The problem I am experiencing is that I want to use a formula in K2. K2 must get its value from a cell on a different sheet.
If I manually delete the value in K2 the rows hide and if I manually enter a 1 in K2 the rows unhide, however I want to enter a formula in K2 that will result in either a 1 or blank cell.
I have a macro that deletes the active row. However if any cell within rows 1-8 are selected then I want the sub to end with an error message i.e. (Cannot delete these rows) The code I have tried is below but doesn't work:
View 2 Replies View RelatedI have an inventory sheet with rows containing a formula. I've placed the same formula in all 65536 rows that Excel 2003 has.
I have a macro to delete unused items. It searches for an item number then deletes that row. (Actually it deletes the item number and a partial row based on an offset, but that was for an earlier version and an entire row would be okay) When a row is deleted, all the other rows move up and Excel creates a new last row (65536) The problem is that this new row has no formulas. While it's probably unlikely that I'll ever blow through 65536 rows, it seems sloppy to leave this unaccounted for.
Is there a way to either add the existing formula(s) to the last row...or insert a new row *somewhere* that is empty except for the formula of the other rows in the sheet? Here's what I have for the existing code.
Code:
'Search for item to delete based on entry to InputBox
Sub Delete_Item()
Dim FindString As String
Dim Rng As Range
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
[code].....
In column A, is a timeline - based on every 15 minutes.
In column H, I use conditional formula to change the cell color of any cell equal to or greater than the value of 00:00:07.
I would like to delete all other rows in column H that is under 7 seconds.
I would like the rest of the worksheet to stay in tact - so I can see the timeline. (or at least column A)
I have tried several macros and vba codes, and it either deletes all the rows or simply does not work with my worksheet.
I am unable to attach a worksheet, but I copy/pasted a simple view. This continues down the page, until 24 hours is complete.
Where 00:00:00 is, I am looking for anything over/equal to 00:00:07 - then delete the rest. The cells are formatted as 'general'.
After performing several operations and sorting the results I end up with a lot if #VALUE! errors in colum B. I would like to search thru those results and delete any row containg "VALUE! in colum B
View 2 Replies View RelatedI'm trying to find a way to delete blank rows that contains formula. I have two excel sheets.
sheet1 contains information and sheet2 references the values from sheet1. Let's say I put the following values in sheet1:
ColA
AAA
(blank)
(blank)
BBB
(blank)
CCC
After entering above values in sheet1, here's what sheet2 would look like:
ColA
AAA
(blank that holds formula) =IF(ISBLANK(Sheet1!A2)," ",Sheet1!A2)
(blank that holds formula) =IF(ISBLANK(Sheet1!A3)," ",Sheet1!A3)
BBB
(blank that holds formula) =IF(ISBLANK(Sheet1!A5)," ",Sheet1!A5)
CCC
What I really wanted to happen is for macro to eliminate blank rows in sheet2 to look something like:
ColA
AAA
BBB
CCC
Is it something possible?
On my excell I have a lot of data on sheet 2 that i automatically pull form a database.
I have this fomula in a cell on sheet 1.
= SUMPRODUCT(--( 'Sheet2'!P5:P50000=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))
The problem is that when the data on sheet 2 gets updated and more lines added, the computer changes my formula for the cell on sheet 1 as well. the new formula will change to
=SUMPRODUCT(--( 'Sheet2'!P5:P50126=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))
when there is 126 lines of new data addes in on Sheet2. Id only 50 lines were added in on sheet 2 then the Formula would change to
=SUMPRODUCT(--( 'Sheet2'!P5:P50050=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))
I have 2 spreadsheets of names (~2500 and ~1800) and a bunch of corresponding data continuing down the row. both are structured this same way:
Example:
Row1: LAST, FIRST, data1, data2, data3, etc...
is there a formula which can "check" the larger sheet for duplicate names (a row with exactly the same FIRST and LAST), and then either:
1) delete these rows from the smaller sheet
2) clear the contents of those rows
3) or at least flag them in some way so I can quickly delete them
it would be quite a task to eyeball and remove these rows one-by-one, so i'm wondering if a formula could somehow do it (I don't really know anything about visual basic)
One other piece of information which might be important:
For these rows containing duplicate first & last names between the 2 sheets, the entire row is not a duplicate entry; only the names will match (columns A & B)... The other columns down the row will have different values between sheet1 & sheet2. Not sure if this changes anything....
I have a macro in which i can enter the rows i want to hide.
If i want to hide "position 32" i have to enter the number 8 of the row. This works fine. But now if i want to hide the "position 32" from Sheet1 it also should hide the rows 4-8 from Sheet2 [Data with 32].
Or if i hide "position 34" in Sheet1 [row 10] it also should hide the rows 14-18 in Sheet2.
Sub Button294_Click()
If Sheet1.Range("A34:A94") = "HIDE" Then
For Each cell In Range("A27:A94")
If UCase(cell.Value) = "HIDE" Then
cell.EntireRow.Hidden = True
End If
End Sub
I would like to rows based on multiple column conditions criteria. ie., if the columns N, O, P values are "", then hide the particular row. The logic given in the website here, i tried But, it is not 100% working. It works for a few rows at the start of the database & it works for the rows at the end of the database. In between, for a few rows, even if the column values are "" it does not hide those rows.
View 2 Replies View RelatedI have the following code to compare two columns and delete adjacent rows if 1 is greater than or equal the other...
Sub LastReceipt_GT_Confirmed()
Dim intLstRow As Integer
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1
With Range("E" & intLstRow)
If .Value > .Offset(0, 1).Value Then .EntireRow.Delete
End With
Next intLstRow
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1 .............
How to skip every second row (delete or hide) of an array of data (two columns) in a worksheet. I have too much data and want to delete every other row at a regular interval (increase step interval from 1 to two or even three). Tried to do this with VLOOKUP and Filter (custom)
View 9 Replies View RelatedI am just thinking if there is a way to delete or hide all vba codes as I dont want readers to see them.
View 4 Replies View RelatedI have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).
The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.
1. Remove J,K,N,A Columns,
2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy
3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value
4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only
5.Merge Column B,C,D,E (SYMBOL.EXPIRY_DT.STRIKE_PR.OPTION_TYP
respectively )
I have a ColA that houses Customer Names...
I need to delete any ROW that doesn't have a customer name SOMEWHERE in the ENTIRE Customer Description...
Say this is the COL A, """Campbell Soup/CA (Queensway)"""
if "Campbell" is in row... then DON'T Delete....
i was thinking how the =find() formula gives a number signifing a "find" within a text, but I don't know how to use that in VBA.
how to automatically delete (or hide) columns in a spreadsheet given a list of columns that I want to retain (or see).
These are the columns I want to retain (or see):
Name
1st Phone Number
2nd Phone
Country
Conditions
Email Address
Enrollment Status
Room not available
Roommate
Mailing address
Payment Record
Payment Status
Gender
Requested room type
Total Payments to Date
What is your meal preference?
The attached has two tabs......columns I want to retain (or see) and the raw data of all the columns.
I have a situation where i would like to hide and delete a few sheets in my worksheet.
there is a few sheets where i want it to be constantly visible and one sheet is based on a cell reference.
the remaining irrelevant sheets i would like it to be hidden and deleted.
'always visible are sheets that are constantly visible in all files' 'visible msds is the sheet where my product data is being pulled out based on a input i enter on my first sheet' the sheets i want to hide and delete will be the remaining irrelevant "MSDS" that i would like to remove through macro.
Using two Combo Boxes, I am trying to create an drop down outline form input sheet.
What it does is if you select the first selection in the combo box 1, then it hides certain rows, and deletes certain cells. If you select the second option in the combo box 1, it shows another combo box (2) which has another list of categories.
When I select the option 2 on the combo box 1, it unhides a bunch of rows, and activates combo box 2. However, when I try to select categories on combo box 2, each time it tries to hide/unhide rows I get this message "Unable to set the hidden property of the range class." I have no idea whats going on.
Here is my ....
i have been trying to hide the userform from the workbook open event with no luck
Private Sub Workbook_Open()
ufmTheEstimator.Show
Dim Worksheet As Excel.Worksheet
If Me.Worksheets("Main Roof"). Name = True Then
ufmTheEstimator.Hide
End If
End Sub
I have a file without password (on file there is sheet protection) & file in shared base with 31 sheets.
User can't do following things :
1. User can't Hide column and can't make a change in column width
2. User Can't delete any sheet from file.
if user do the above thing then undo the event or stop the above event.
This file is on shared mode and i don't want to use sheet protection