What I need VBA to do is scan the data in a certain column (in this case column I entitled "Type") and if it picks up a certain letter (either a "C" "D" or "K") in a range starting from row 2 then Delete the whole row and condense them so I am only left with the data that corresponds to letters "E" and "R"...
Does there exist a macro that can accomplish this for me; the alternative doesn't even bare thinking about as the range can be anything up to row 6k
I have tried looking on the Net and have seen example of similiar macros, alas I'm completely new to this and cannot adapt it to my needs.
I can trying to let the user type a word in to cell A1 and then have Excel remove the rows that contain the word. But VBA below just seem to delete all nonblank rows?
sub delete2() Range("a2:a200").Select For Each cell In Selection If cell.Value = A1 Then cell.ClearContents End If Next cell Range("a2:a200").Select Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete end sub
I have some code that works almost perfectly. I am trying to automatically delete rows in which no data exists in cells in a certain column.
the problem that I am having is that is seems to go into an infinite loop.
For example, if I delete row 5 and then iterate to the next row, row 5 becomes what used to be row 6 and now my code is on row 6...essentially skipping a row.
Here is an example data set (note that the set is for 20 rows): 1 3 6
15
36 37
here is the Sub Macro2()
r = 20 Min = 1 Do While r >= Min If Cells(r, 1) = "" Then Cells(r, 1).EntireRow.Delete Else r = r - 1 End If Loop
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.
I am trying to educate myself in the wonders of Excel (2003) and an struggling somewhat.I have a small project loosely based on a shops stock control and have attached a copy of the workbook -
The user normally only has access to columns B, C and E due to the others being locked and the sheet protected.I would like the sheet to be automatically sorted based on column E each time the workbook is opened.I then need to automatically delete a row once an item in column B has been removed (made blank)
I have a file and that file has two different tabs. The result in first tab comes from tab two. Every time the columns in tab two changes. Is there a way that any time the column in tab changes, the same is effected in tab one. Eg. For one month there are 8 tabs and 9th is for total. for second month there are 9 tabs and 10th is for total can that effect be done in tab one accordingly?
I understand how to email from Excel, but what I need to know is how to automatically delete the emails I just sent from my sent items. I use the code in Excel to email out many reports with attachments every morning, and need to delete them right after I send them to conserve space. Can this be done form within Excel code?
Our company has designed a Excel Document for quoting purposes. The problem we are having is that certain sheets that are needed to do the quote do not need to be used after the quote is finished and saved. These extra worksheets are taking up a lot of room on our server. In the document I have already disabled the save feature so that the saveas has to be used. Is there code that can delete sheets 3, 5, 6, 7, 8 upon doing a save as?
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.
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 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 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.
Need to solve my problem in the thread "Type Mismatch Error Message". Now a new problem has come up in the same code, so - according to the rules - I've started a new thread. (This one is most likely due to my poor knowledge of VBA syntax).
Sub Delete_invalid_rows() Dim i%, j% Dim Nr%, valid As Boolean, BYPdata As Boolean Dim ar1 As Variant Dim ar2 As Variant Dim ar3 As Variant Dim ar4 As Variant Nr = 20 ar1 = Array(11, 14, 19, _ 20, 22, 25, 26, 27, 28, 29, _ 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, _ .................
This sheet has A:K columns and 1:3212 rows. There are 'page headers' that are in the text file that I want to delete (the text file was exported from an AS400 program). The first row that starts the page header has SA341 in column 1. Each page header has 5 rows. I used this code from one of the other threads on deleting rows, but I obviously do not understand the code as it deleted all rows that contained SA341. Sub DeleteRows() Column_To_Check = 1 Start_Row = 1 End_Row = ActiveSheet. Cells(Rows.Count, Column_To_Check).End(xlUp).Row MsgBox End_Row Search_String = "SA341" For Row_Counter = End_Row To Start_Row Step -1 If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value < SA341 > Search_String Then ActiveSheet.Rows(Row_Counter).Delete
I am copying a price list from a worksheet. I currently have a script that deletes unwanted rows (products) but these products' header rows' are left. I also want to delete these text based headers. One solution might be a script that reads a columns cell value in the row(s) below and if values are missing the header row should be deleted.
I need to know how toformat my spreadsheet so it will automatically give me a new row when I reach the end of the column instead of having to hit insert row to add one.
I would like to write a macro button, in order to lookup through col. "D" and where find same invoice number (invoice number combine with 3 letters plus dash), should sum the corresponding rows' amounts in col."J" into one row and the other row should be deleted.
I have a sheet as shown in the picture below. If the cells in Column N has some values that are the result of some calculations. How ever if there are more than one of it, the entire row has to be deleted.
Eg:The value in Column N 816.323 has 3 rows below each other.Only one has to be there.There can be upto 10 values one after other in this way.
I tried to write a progrma by using loops and cehcking if ther is value in the cell,go to cell below and if there is value,delete the row in a loop of 10 times.But it didnt work
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 two separate tables, one above the other, and need a way for it to automatically shift the second table down or a row between the two tables any time another row is added to the top table. Is there any way to do that?
