Find The Column Name Purple & Delete The Whole Column If There Are No Blank Cells
Mar 18, 2009
I'd like a macro that does 3 things..
1. Find the last row (cell) of data in the "Customer Number" column. This search should be by the name "Customer Number" rather than by column letter because the column that "Customer Number" will be in can change.
2. Find the column named "Purple" (also by name for same reason)
3. If the "Purple" column has no blank cells in those same number of rows as the "Customer Number" column, delete the whole "Purple" column.
I am manually scrolling through Excel right now to find all blank cells in column L. Some cells have dates, others are blank. IF L is BLANK, I go to row K and copy/paste row K to row L. IF K is BLANK, I got to row I and copy/paste to row L. Is there a statement I can put into row L to basically say "If blank copy/paste from row K. If row L and K are blank go to row I and copy/paste to L." ???
I would like to search Column C for an instance of the text "Std. Residual", then cycle through the non blank cells to the right and run some formatting code:
VB: 'this line will be modified so that i cycle through the non blank cells that i have found For row_cycle = 1 To 7 'not signifcant If Abs(ActiveCell) < 1.96 Then Selection.Interior.Color = 255
[Code] .....
I then want to search for the next instance of "Std. Residual" in Column C, do the same again, and so on for the entire Worksheet.
I am working with arrays that extend far beyond their actual content, and so i am looking for a way, through macros, to find the first blank cell in a column and then copy all preceding cells in that column.
I want to do, is search column A for claim numbers that match. When I do have a matching claim number, I want to concatenate the original cells ownership field with the said matching cells ownership field (or move into a column in the same row, I can always concatenate later). Once that is complete, I want to delete the row I took the information out of.
I want to join this data in ArcGIS, but as of right now, it's not a 1-to-1 relationship, so only a relate works. That doesn't help me as I want to display claims by ownership, and this can vary per claim. Company A may have 100% on one claim, and then split another claim 50% with Company B.
This causes a double entry on the claim field in this current spreadsheet I have, which requires me to clean it up by making multiple columns of ownership vs. an additional row for shared ownership.
My problem:
Column A Column B 1235555 Company A (50%) 1235555 Company B (50%) 1235556 Company A (100%) 1235557 Company A (33%) 1235557 Company B (33%) 1235557 Company C (33%)
What I would like to see
Column A Column B Column C Column D 1235555 Company A (50%) Company B (50%) 1235556 Company A (100%) 1235557 Company A (33%) Company B (33%) Company C (33%)
I would like a macro to do this...If a cell in column G is blank and the cell in the same row in column C is NOT blank, highlight the blank cell in column G Red.
I need the search to stop ONLY when it gets down to the bottom-most row of data in column A.
Note: Any row headers will always be in row 1 only
In sheet "diary" I have data in certain rows in column A8:C10000 that contains values if a certain condition is met. I need a vba to copy and past only non blank cells in column D8:F10000. I first wrote a formula with index but it takes too long to caculate.
I've recorded and edited a macro for a worksheet I have, but there is one function I can't figure out - if it can even be done.
My sheet has 134,000 rows of data extending to column AS but, in column K, I have approximately 9 - 10,000 empty cells. I need to delete the entire row of data where there is an empty cell in column K. Can this be written into the macro?
I need a macro to copy and paste data from Col A to Col B. But I need it pasted 1 row up. In other words if A6 has data I need it pasted in B5 and Bolded. I got a start but don't know how to finish.
Dim ii As Long For ii = lastrow To 6 Step -1 If Not IsEmpty(. Range("A" & ii).Value) Then ****.Range("A" & ii).Copy***** Next ii
Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals".
In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole.
If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.
I am using the following macro to delete "completely empty" rows. I also need to delete some rows if a cell in column B has no value. How would I change this macro? ' DeleteBlankRows
Dim r As Long Dim C As Range Dim Rng As Range
On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual
If Selection.Rows.Count > 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If For r = Rng.Rows.Count To 1 Step -1 If Application.WorksheetFunction.CountA(Rng.Rows(r).EntireRow) = 0 Then ActiveSheet.Rows(r).EntireRow.Delete End If Next r
I am trying to delete rows that do not have values in column B. This is a quote form that takes up over 1000 rows but not all are needed (ie lines that do not have any value in B "qty"). Is there a way do have excel delete these without doing it manually?
way to find blank cells in a column(s) and delete the rows corresponding to the cell number. I found this code in one of the previous threads on ozgrid but it just doesnt work for me because it is slowing me down. Please help (I am working on around 60000 rows in Excel 2007)
Sub DeleteBlankARows() With Application .Calculation = xlCalculationManual . ScreenUpdating = False Dim r As Long For r = Cells(Rows.Count, 11).End(xlUp).Row To 1 Step -1 If Cells(r, 11) = "" Then Rows(r).Delete Next r .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub
a way to do the reverse viz. for a particular column, I'd like to delete the row corresponding to the non-blank cell in the column.
I have this sheet(sheet2) with some data , I have 2 macro, my problems at this time is that I am looking for macro that delete only the blank rows that under the rows with {SELECT ....} and the under the blank rows with {CELL-ENTER......} and all the rest blank leave in place , I am also looking for option to integrate the new macro with the existing one and come up with one macro that I can refer to click button , in case that it is unfeasible , it's ok with me , I just will call the other macro at the from the first macro that I have.
I have data input in a particular column, say Column C.
I would like to have a macro that deletes the rows where the corresponding entry in Column C are blank.
In addition, there are multiple worksheets with the same data format in the same spreadsheet, but the number of row varies. It would be great if the macro can delete the row with blank cell in that column across all worksheet.
I'm trying to delete rows which has columns that appears to be blank. Below code worked fine but it took about 15 min for the macro to work through the spreadsheet.
Is there a way to delete any text or formula from an entire row, based on column A? In my spread sheet I have multiple rows where column A is blank, but column's F, G, and H have formulas in them. I would like to use VB or any other method that would be best to clear the contents of all the rows where column A is blank.
Is there a way to delete or clear a row on exit if no end date has gone into it. i.e. if the user clicks on the X button in the top right hand corner.
Every time the user clicks on start the date goes into the next empty row, if finish is clicked then the end date will go into the cell next to it. See example below. Here the user has started and ended correctly
column AB StartEnd 24/10/09 24/10/09 24/10/09 24/10/09
In the example below the user has forgotten to click on the finish button when they first clicked on start, they exited excel instead and no end date was inputted. When the user clicked on start again the start date went to the row below, however as there was no end date the last time, this time when they clicked on end button, then end date went to the first BLANK CELL
column AB StartEnd 24/10/09 24/10/09 24/10/09
I would want the delete row or clear row option, which ever would work best, to happen if the user clicked on the X in top right and corner or Exited excel via File.
Ideally I would like the row cleared and not deleted, I have read Clare Watts thread and we both seems to be having a similar problem, I have used some of the examples from her thread but non seem to work. So I now wish to go with a delete or clear row option on exit, if possible.
The row changes every time. The column for deleting or clearing would be A to AB. The end date goes in to Column B
So if the user clicked on start but did not click on the Finish Button then the last row with no end date would be deleted or cleared on exit from column A to AB
column AB StartEnd 24/10/09 24/10/09 24/10/09(This is deleted or Cleared of exit via X top right hand corner)
I have an Excel worksheet with two rows. Row one has months, row two has unit counts. I simply want a formula that will find the first (leftmost) date where a unit count occurs, returning the month of the first unit data point. I've seen several forum questions on how to search/ lookup the right-most non-blank/non-zero data point, however I would like the first, leftmost point. I have been unable to modify the forumula.
I am looking for a way to find the first blank cell in a column.
Range("A2").End(xlDown).Offset(1, 0).Select
The problem is that there are no 'blank cells because they have a formula in them that checks a different sheet for data. If there is data then it simply copies that data. If there is no data then the value of the cell is "". So the cell shows blank but in fact it isn't.
So how do I find the first cell that don't show data because of the formula that resides in the cell? Here is the cells formula..
