I have several lists that I need to be able to print out periodically.
The main worksheet that underpins each list is over 900 lines long and numerous calcs and formulae populate columns to provide the data for my 'print lists'.
I need my 'print lists' on seperate worksheets and I have linked the data from my main worksheet results, using IF statements to stop unwanted results being displayed by making the rows appear empty (i.e. to display "").
Due to the design of my main Worksheet it is inevitable that I have many rows in each 'print list' that are not required for that particular 'print list'.
I want to remove the 'empty' rows from the print sheets at the time of printing. I cannot use the GoTo > Special > Blanks > Delete Rows because the rows are not recognised as being 'blank'.
I have attached 2 screenshots of my 'print lists'.
P.S. I am not assuming anything but I have searched on OZGRID and it appears to me that I might need to use Excel/VBA?
I went to the Macros page and pulled this macro to remove rows if they have blank cells:
Sub DeleteBlankRows2() 'Deletes the entire row within the selection if _ some of the cells WITHIN THE SELECTION contain no data. On Error Resume Next Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete On Error Goto 0 End Sub
I ran this and nothing happened. Does anyone have a fix or perhaps a better way to make this happen?
I would like a macro that deletes rows based on having two blank cells in adjacent columns. I have achieved this with the following code however i need it to only delete rows below a certain row. How would i achieve this?
Sub DeleteBlankARows() Dim r As Long For r = Cells(Rows.Count, 30).End(xlUp).Row To 1 Step -1 If Cells(r, 3) = "" And Cells(r, 4) = "" Then Rows(r).Delete Next r End Sub
I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
I want to copy an active cell in row 2 (I dont want to fix a cell as it can be any cell at any time). I want to copy it all the way down to a cell where the next row after it, is completely blank on all the 256 cells.
I have a spreadsheet in which I needed to add the row totals of cells affected by conditional formatting. Ozgrid came to the rescue here (kudos to Dave H and JimFuller1), but now if I sort by these totals the results do not match the rows they are associated with. The attached spreadsheet shows the values the users want to see on Sheet1. The user changes the highlighted values in the rows by changing the values in A1 and B1. These index a set of tables on Sheet3, and Sheet2 mimics the CF conditions in order to provide the totals in col V of Sheet1.
Now if I filter a Sheet1 field on nonblanks (to get rid of the blank rows) and then attempt 'Sort Descending' on Col V, all the rows appear to sort correctly with the exception of Col V which now appears to give the wrong total for the row.
I am looking for some VBA programming that will allow me to use 1 button to consolidate rows (delete them). The problem I am running into is that the rows I want to delete have formulas in them so I can use "find all blank cells & delete" type of macro.
Attached are a couple of brief images on a small scale what I am looking to do.
I want a user to be able to have this sporadic list of choices, consolidate them so the are all one right after each other, then export to a word file for editing later on.
delete rows of a report which contains $- or zero value but not row with Beginning Balance. Data begins from Column B to Column E. Some records may need to be deleted completely from Account Number down to Subtotal rows plus one empty row, if the Subtotal amount is $- or zero. Below is the sample data and how the finished sheet should look like. The report will comes in thousand of line. I don't have knowledge in programing but believe it can be done through VBA.
211-00-5936-00-001-3-00-2-00 Trans. Date Doc. No. Trans. Line Comment Actual Amount
if Subtotal is $0, then delete the row starting from Account Number to Subtotal plus one empty row
211-00-5936-00-002-3-00-2-00 Trans. Date Doc. No. Trans. Line Comment Actual Amount
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.
I have 4 cells in a single row. I would like the first 3 cells to contain various data for assigned tasks to employees. The 4th cell will contain a date, but that date will only be inserted once the task has been completed. The behavior of the cells over time needs to look like this:
1. The first 3 cells must have text in them and remain with white backgrounds until the date is inserted in the 4th cell. 2. All 4 cells must turn gray once the date is inserted in the 4th cell.
A user on another forum gave me Cell Value is Not Equal to ="" as the answer. This does not work as I have text in the first 3 cells and need them to remain white until the date is inserted in the 4th cell. Using "Formula Is" makes more sense to me as an approach, but I'll be hog-tied if I can figure out how to write the formula for it.
I can't figure out how to make this formula work because I need to use cell AG46 and it contains an IF formula that leaves the cell blank if the sum = 0. I keep getting the #VALUE! error. How do you create a formula using a cell that has an IF formula that could leave the cell blank?
Basically this is what I need in cell I12: =sum(a12)-(ag46*8)
I have the column E in excel labeled 'Notes' in tab 1 adding at the bottom of the column using(=COUNTA(E2:E29), so if the cell is empty it doesnt add it up in the totals. In tab two i have that same column linked via formula. In tab 2 its counting the empty cells because even though they appear blank they have a formulas in them how do i get it to not count those cells. here is the formula im using in column E tab 2.
I have been trying to delete about 86k rows in my table in a worksheet. It has been over 5 hours now and it is still running to delete. any better solution? or how long do i need to wait for the system to finish its work?
I have a real problem with a file I'm working on. It has invoice numbers in one column, followed by payment milestones. In the row underneath, there is an 'x' to mark if payment was made in a particular zone, e.g.:
The current worksheet has data which is retrieved from other worksheets.
Current worksheet A1= Sheet1!A1 Current worksheet A10= Sheet2!A1 Current worksheet A20= Sheet13!A1
The range of this current worksheet is A1:F1287 and inbetween there are blank rows. The cells in the current worksheet are not technically blank, because each cell (A1:F1287) retrieves the information from the respective worksheet.
I would like to know of a VBA code to delete a whole blank row/-s (all columns of this row is blank) inbetween the range. Therefore, if there is a whole blank row, this row to be deleted and to go to the next row that shows information . In other words, instead of me manually searching and deleting whole empty rows; a VBA code for this task.
I am trying to write a macro that will check from 1 to 143 columns..and if all the columns are empty then it has to delete that entire line. Totals rows are over 35000. I am using excel 2007. I have written the below code. Could someone pl help me in enhancing this.. or a better way as this is taking about an hour to complete.
Sub Costa() Dim i As Long Dim j As Integer j = 2 For i = 2 To 37735 loop1: For j = j To 143 If Cells(i, j) = "" Then j = j + 1 GoTo loop1 Cells(i, j).EntireRow.Delete Else j = 2 GoTo loop3 End If Next j Cells(i, j).EntireRow.Delete loop3: Next i End Sub
i am wondering if there is a way that i can have these cells that depend on formulas to remain blank, until i have my data entered, or that they may remain blank, but give me running totals of only the cells i have inputted.