When I run it the message "could not detect range" comes up. I selected my desired range then ran the macro and did not get the desired results. It looked like maybe only one row was deleted. My data has seemingly random blank rows between it, some small row ranges, some very large.
I am trying to detect if a user leaves a cell empty. I have tried If statements like:
If Target.Value = "" Then
and
If (IsNull(Target.Value) Then
The problem is when the user uses the spacebar to delete an entry in that cell. If they leave any spaces after exiting the cell those if statements fail to run.
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 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 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 problem is that there are two rows with data, a blank row, then another two rows with data. I have thousands of rows and need a quick-fix to delete the blanks.
The current worksheet has data which is retrieved from other worksheets.
For example:
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.
modified my code to have the data continue to the next row where it left off before jump to other sheets. The code below creates too many blank rows of all sheets (9213, 9316, 9501 and 9601).
After the code stops execute, I have to delete all the blank rows. This takes too long, approximately 5 minutes for each sheet...
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 have created a file where I use the Subtotal function. Once I collapse the information to only give me the Total, I would like to copy the Total rows into another worksheet. However, when I do this I get blank lines in between. I am trying to find a way to delete the blank rows in between the Total rows I need. Is there a way to do this with the auto filter function?
I need to loop through data when it finds a blank it need to delete that row and 15 rows down and loop through whole data set find blank row and delete 15 rows down.
It's been years since I did any VBA, and I forgot. Do I need to use offset to acheive this task?
I need to write a macro that will successfully delete blank rows. I also need to write a macro that will successfully delete duplicates. The duplicates are numbers and they do constantly change.
A macro that will delete a tab or tabs in a file if and only if rows 11, 13, 23 & 25 are completely blank within that tab? So basically ALL rows would need to be blank, if there is any data within any of those rows, then tab should NOT be deleted.
I have a spreadsheet that has 4 columns and column D has some blanks randomly down the sheet. How can I delete any and all rows that have a blank in column D?
I need a Macro that will delete rows within a specific range that contain blank cells or preferably delete the rows where the first cell in the row contains a blank cell.
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 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?
In column A I have a list of Acronyms from A2:A90000 and more
In column B I have the corresponding acronyms spelt out from B2:B90000 and more
When I run the macro, it shoud detect the multiple/duplicate Acronyms and it's corresponding descriptions, DELETE the multiples/duplicates and move the cells up.
I have command button in Sheet 3 (from where I will be running the macro) and I need a macro which will delete all the blank columns and rows in the data containing in Sheet 1.
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've added a button with a macro to delete any blank rows in a worksheet.
Cell A3 is completely blank and A6 has an IF formula which returns "". In my macro it copies the whole worksheet and then paste special values, to take away the formula in A6 and then deletes the blank rows but it still does not delete row 6.
I'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
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.
