Delete Rows And Columns Entirely From Worksheet So It Shows No Cells
May 9, 2014
Please see this attached image: HowTo Do This.PNG
I have this worksheet that I did not create and notice it has entire rows and columns completely gone / deleted. For example, the only columns it has ranges from A to AI and then no more columns exist in the worksheet. For the rows, it has rows 1 through 77 but then it shows a gray background.
How did they do this? What is this called? How can I do this to Worksheets?
I am looking for something that will first delete all empty columns up until it runs into data, then delete all rows that are blank in Column A until it runs into data. In other words, I recieve different spreadsheets that start their data in different locations and I would like to have them always start in cell A1.
My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.
removing 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.
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.
I am attempting to delete rows with columns not containing "MC". In the example below, the result should be 3 rows remaining (1,2 and 5):
Y Z AA
IP/DA/SV
MC/SWL/SA
MC/SH/SA
[code]...
Instead, all I get are results with Y starting in MC, in this case the last row, all the other rows are deleted. I need to get all rows that have MC in three columns (Y, Z or AA) and delete the ones that do not. In this case, rows 3 & 4.
Below is the code I have been using:
Sub Del_Rows() Application.ScreenUpdating = False With Range("Y1:AA1", Range("Y" & Rows.Count).End(xlUp)) .AutoFilter Field:=1, Criteria1:="*MC*" .Offset(1).EntireRow.Delete .AutoFilter End With Application.ScreenUpdating = True End Sub
Seems simple enough but this newbie chump is stumped why it is not reading the other columns
I tried to modify this code for my next problem: to delete entire columns where the cell in row 2 is empty, but it doesnt work. The code I use is:
Sub test2() 'This code will create a copy of the current sheet, and retain only the rows 'that have the value '1' in column B.
ActiveSheet.Copy Before:=ActiveSheet Application. ScreenUpdating = False On Error Resume Next Dim ir As Long, mrows As Long, lastcell As Range Set lastcell = Cells.SpecialCells(xlLastCell) mrows = lastcell.Row 'Note rows are deleted from the bottom going up For ir = mrows To 1 Step -1 If Len(Trim(Range("b" & ir).Value)) = 0 Then Rows(ir).Delete Shift:=xlUp End If Next Application.ScreenUpdating = True End Sub
The ideal solution would be not a second macro but one that does firstly delete the rows and then the columns.
I have a single workbook with 55 worksheets. I need to delete rows #1-14, then delete columns A & B on all worksheets. The macro below seems to be running in a loop which appears to delete rows 1-14 and columns A & B until there is no more data left on the active sheet only. It does not run on any of the other sheets - even if I select all sheets before running the macro.
I have a large spreadsheet that is 65000 rows and colomns A thru W. I need a macro to delete rows that if Date and Account and Type are equal and the net of Quantity is zero delete those rows.
Col A = Date Col C = Account Col D = Type Col J = Quantity
What the macro would do is if an account had 250 rows of activity on say 7/7/2004 (Date) and same type of account and the net of all Quantity is equal to zero delete those rows.
I have a spreadsheet that in column A has either the letters "C", "LP", or is blank.
In Column L it has either numbers 1 through 100, or the letter "D".
What I need it to do is to check and see if the data in column L is "D" and in the same row column A is blank, then delete that row.
Also, if the data in Column L is "D" and the Data in column A is either "C", or "LP", then I need it to change the "D" to "1".
It might be easier to do it separately because once you run the part that deletes the rows that don’t have a “C” or “LP” then I could probably just do one that says if column L = “D” then change to “1”.
Lets see if I can simplify this for you.
If Column L = “D” and Column A = BLANK then Delete Row (this would delete all “D’s” that don’t have the “C” or “LP”)
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB: Public Sub DelRows2() Dim Cel As Range, searchStr, FirstCell As String Dim searchRange As Range, DeleteRange As Range
I have a workbook where I import data from another workbook. This might get done several times a month. As I go through the imported data I delete rows not needed which gets place into another worksheet. What I need is when I import data again is a macro that the worksheet where my already deleted rows are and compairs them to the new data imported and if the first 13 columns of data matches it delest them off the imported sheet.
I found some code which I altered at [url]
and posted the same question but haven't got any response as of yet.
So the code that i go works but only if the row matches in the exact order on both sheets so e.g. (if row 1 on sheet1 matches row 1 on sheet2) it works but if (row1 on sheet1 matches row 3 on sheet2) it does not.
Here's the code I have so far.
Public Sub delOLD() Dim i As Long Dim iLastRow As Long Dim ws As Worksheet, ws1 As Worksheet
Set ws = Sheets("Data") ' Imported Data Set ws1 = Sheets("Old Records") ' Deleted Data
I have anywhere from 3000 to 10000 rows to delete in a a number of worksheets, but would like to avoid using a loop as even with onscreen update turned to off like this:
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.
Is it possible to delete multiple rows when the worksheet is protected using vba? For Example Peps - Select a undetermined amount of rows and then use a macro to unprotect - delete the selected rows - protect.
First problem: in column B, I have a formula based on contents of column A. However, the limitations of 7 nested IFs leaves me with errors - I need 3 more IFs. Is there a way around that?
Second problem: I need to show the row of the first (earliest time in column A) and the last (latest hour in column A) record for each name. I've been sorting by name and time, then manually deleteing the rows I don't need. I know there's a better way, but I can't find it!
I need to remove all rows where COL A value and COL B value are the same. COL C does not need to be considered. However I need to retain one of the Col C values for purposes of formatting.
The end result should look similar to columns F,G and H!
Is there a formula I can use, where I can delete specific rows/columns each time? I have to download a large set of data each week and each time I have to spend hours deleting the same rows and columns. For example, if I have to delete Rows 1,2,3,4,5 and Columns J,I,H,G every time, how do I automate this?
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 a spreadsheet with hundreds of rows. Columns C and D contain either TRUE or FALSE. I want to be able to automatically delete the rows where both columns are FALSE.
I'm trying to come up with a formula to find the last row without any data in columns E and F and delete the 10 rows below them. Is there a way to do this?
I am trying to use a macro to delete 2 columns from a spreadsheet using a macro.
This sounds easy, but due to the fact the columns needing to be deleted ‘cross’ merged rows the macro then deletes all the columns based on the merged cells.
In the attached spreadsheet (ColDel) if you manually highlight columns D and E and then use Edit/Delete this will delete columns D and E correctly.
