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 RepliesI 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'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 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
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 )
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
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies View Relatedremoving duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
In my excel I'm copying rows upon specific criteria to another tab.
The question is how I can delete copied rows and update the original tab without empty rows? (N of rows is always changing)
Is there a limit on the number of rows and columns that can be deleted in a macro on Excel 2003? I am trying to create a macro that, amoung other things, delets 1119 rows and 54 columns. If I delete the columns first, the rows will not delete. If I delete the columns first, the rows will not delete.
View 12 Replies View RelatedI am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.
My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.
So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
[Code]....
My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.
I need a macro that can look at multiple (say three) rows and delete those rows if they match another three rows in the worksheet.
For example:
1 0
3 1
5 7
4 4
6 5
8 3
1 8
5 2
3 9
6 5
8 3
1 8
7 5
If three rows are the same, then one set is deleted (it can be either bottom or top set). I would like to do this for rows 1-500 in the worksheet.
I 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 a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:
1- all rows with the value "TOM" in column C will have to be deleted.
2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5
Should be processed and come out like this:.......................
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 worksheet that has a quantity column "A" and if there is no value in it I would like to hide all rows without values so only rows with quantities remain. Based on the code I have supplied below you will notice I have ranges of cells that I want to work with. The code I have works well with the exception it takes a long time maybe 30-45 seconds to perform the operation. I was wondering if there is a faster way to perform the function. I have saved it as a macro which I tied to a button.
[Code] .....
I have sheet 1 and 2 in a work book. Sheet 1 is a list of data consisting of names and address, and quantity, type, price, etc of items shipped to them. Sheet 2 takes the data from sheet one and computes certain formulas. I have allotted up to 100 rows of data to automatically compute, however I rarely need that much. Basically it goes like this. When I open the spreadsheet sheet 1 is not actually blank, instead in cell A1 I have the phrase "Customer Name", in cell B1 I have the phrase "Account Number" and so one. I do this so my coworkers know which info must go in which cells. As long as the info is put in the correct spot than cell A1 in sheet 2 with do X and B1 will do Y and so on. so if I put in 10 customers with accompanying data then the first 10 rows of sheet 2 will compute formulas and the other 90 rows will simple state "No Data" in the cells. What I would like to do is create a macro or assign a command button to HIDE all the rows that say "No Data" in column A. So if I have 30 customers entered then when I go to Sheet 2 all I have to do is hit a button and rows 31-100 become HIDDEN, not deleted.
View 3 Replies View RelatedI preferrably want to hide all rows from row 58 and down, but then unhide some rows. The rows that I want to unhide is from row 1000. How many rows that I want to unhide, depends on the content of that list. If the list is empty, then I want to unhide rows 1000-1003.
What I have written (but doesn't work), is:
Code:
Range("B58:AG1048576").Select
Selection.EntireRow.Hidden = True
If Range("C1002") = Empty Then
Erstatningsgrunnlag = Range("B1000:AG1003")
Else: Erstatningsgrunnlag = Range("C1001:AG") & Range("C1001").End(xlDown).Row + 1
End If
Erstatningsgrunnlag.EntireRow.Hidden = False