Column A's rows are merged according to column B. E.g.
Column A:
Row 1-7 (merged) Ron
Column B:
Row 1: New Jersey
Row 2: New Delhi
Row 3: New Jersey
Row 4: New York
Row 5: New York
Row 6: New Jersey
Row 7: New York
My result should be like:
Column A:
Row 1: Ron
Column B:
Row 1: New York
and similarly for other rows also.
PS: The no. of rows merged for column A can be different.
Also the keyword that should be present in Column B should be according to hierarchy, New York, New Delhi and then New Jersey i.e. first the macro should check for New York, if its present (in 1st 7 rows according to example given) then New York should be written in Coulmn B, if New York is not present then it should check for New Delhi and New Delhi should be written in Column B and if New York and New Delhi both are not present then it should check for New Jersey.
There are only 3 keywords which need to be checked i.e. New York, New Delhi and New Jersey.
If these 3 keywords are not present then the macro should not do anything.
I need to build a macro which will look for a specific word say :"ABC" in a particular column say "B" and delete 5 rows including the row containing the word "ABC"
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.
I get a report each week that has a bunch of unwanted header info. It used to be static and the macro just deleted the first 49 rows. I recently found out that the header format changed and it's been deleting crucial data.
I need something that will find the words "Medical Center" and delete all the rows above it, however many that may be...
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 a spreadsheet that I need VBA Code to do the following
1) Delete all rows where the word totals appears in Column B for eg "Totals for Vehicles Stocked in 0706" "Totals for Vehicles Stocked in 0707" etc
2) Delete all rows where the value in col m is less than 45
I have written the code, but cannot get it to work-see my code below
Sub Del_TOTALS_Underaged() FinalRow = Cells(65536, 2).End(xlUp).Row For i = FinalRow To 1 Step -1 If Cells(i, 2).Value >= "Totals" Then Cells(i, 1).EntireRow.Delete If Cells(i, 13).Value < "45" Then Cells(i, 1).EntireRow.Delete End If End If Next i
I am trying to find a macro that deletes a row that does not contain a given word or string.
For example, if a row does not contain the string -> then delete it or even better select and move all such rows to another worksheet so that the main worksheet is left only with rows containing the string.
I need to find the word 'numeric' in the merged cells of first row (always first row) and to select its corresponding 2nd row data using macro. In the attached file, M2 to P2 data to be selected.
I have an excel file, where there is a section "Additional Requirements". This section is repeated like 20-25 times in the sheet and has a group of merged cells below it to enter data as shown in the picture. Issue is that majority of these "Additional Requirements" are not filled by the user. Hence maybe only 3 or 4 are filled and I have to manually delete the rest. I tried the code below, but it doesn't do anything.Running the macro just jumps the cells slowly downwards the sheet. Maybe if the macro runs 10-15 times it reads the whole code and then moves on to the next row.8-1-2014 12-43-27 PM.png What I'm trying to achieve is:
1. for the macro to find "Additional Requirements"
2. Then check the Merged cell below it (thus the offset)
3. If this cell is blank, select this cell and the cell containing "Additional Requirements"
4. Delete entire rows of these cells/delete the selection (entirerow.delete). (I read somewhere to set a variable as selection and clear it, hence ive included in the unreadable code. This wasn't working either)
I got a code to delete all rows in the sheet which contain the word "DETAILS" but I now want to delete all the rows that do not contain the word "DETAILS"
My code if needed is:
Sub Find_details() Dim rng As Range Dim what As String what = "DETAILS" Do Set rng = ActiveSheet.UsedRange.Find(what) If rng Is Nothing Then Exit Do Else Rows(rng.Row).Delete End If Loop End Sub
I am trying to make a simple program that will go down column A deleting the rows until it meets a cell with the word "Date" in the A column. When it has found that row I want it to delete that row also and then stop.
This is what I have so far
Sub FindAndDelete()
Dim row As Integer Dim col As Integer
ActiveSheet.Range("a1").Select
If Range("a1") "date" Then Rows("1:1").Select Selection.Delete End Sub
I am trying to populate the 2 tables from excel to word. I will be getting the excel file with tables in various sheets. One sheet consist of 2 tables that will be inserted to one word document. So if there are 2 sheets then I will have the tables inserted in the 2 word document. In the excel sheet I have attached, there are 2 sheets with tables in each of them. I have written the code to copy and paste the table to word doc from (general) range A1:G4 (Table 1) and A9:H18 (Table 2) that has empty rows and columns selected. But there are empty rows and columns inserted since the table range is not same sheetwise. I would like get the empty rows and columns deleted in the word table.
Find the attached sample excel sheet and the word documents.
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, _ .................
I am generating a calendar view of project information in excel with the column headings as dates. Every project should be a merged group of cells spanning the rows for the proper dates.
Right now I generate a new row with new merged cells for every project (100 projects = 100 rows, even if the project only spans a single day!)
I need to be able to consolidate the project data so that if two projects' dates don't overlap they are put in a single row.
I have a huge spreadsheet that I want to be able to sort through and delete the unwanted rows. I want to do a search for anything in column C that equals 2225 including the next row after and delete the rest. Here is an example...
I have a table of data A2:H1000 (this gets longer every week). Within that data every so often i have a row which is blank from A:G, but has a value in H.
I want to create some code that checks the rows and if the cell A in the row is blank, to delete the entire row.
If I have a table (see below), is it possible to create a macro to delete rows that do not have defined data. In the example below, I would want to delete rows 4 through 7 as there is no date in Columns B through D.
A B C D 1 2008 100 200 300 2 2009 500 600 700 3 2010 800 900 1000 4 2010 5 2011 6 2012 7 2013
My merged cells are not purely aesthetical - each merged cell (covering 6 rows in total, beginning at A3:A8) has colums and rows to the right for other details/formulae relating to that specific merged cell (which contains a customer account name).
The list I'm trying to reference is on a seperate tab - 'Projects' starting in cell A4. This list will have project names added to it, so I need my merged cells to reference the list, and subsequently pulling through all new additions to the list, rather than having to add them on a 2nd time.
I'm trying to add rows in range that has merged cells and unmerged cells combined
The number of rows that is already available before adding is dynamic so can be different each time
Set up is as following
columns A merged, B merged , C merged, D not : free rows , E merged again (merged start up is from 1-6 but as stated should become dynamic each time a row is added in column E
same set up is repeated from row 7-12 , etc....
so I i'm in block A (combo of merged colums and free rows in E ) one row should be added in colum E (E would then be 1-7 and be part of block A) same for the other "blocks".
I have a formula that does exactly what I need , it fetches negative stocks from sheet 1 , but the formula places the data exactly on the same row where the negative is , thus there are a lot of in between data not required , Yes I can copy paste special values and remove blank rows , but the macro will do it faster
I have put together a macro that will delete rows (called DeleteUnwantedRows) in a spreadsheet that contain the phrases "no further action" and "not applicable" ... However the macro does not appear to be working.
What I need is a macro to delete a row or rows for a specific range of cells (eg. E20 to E58) when the cells in that range column E is = blank. The cells in column E is formated with a currency sign $. Deleting the row should shift up all rows below. I need to run this macro manually. Excel 2002 SP3.
I have attached a worksheet to illustrate a "Before" (raw data) and "After" (desired results).
Each row contains data on a forex trade. The variables which must be assessed in each row are:
Currency Pair (e.g. GBP/USD). Opening Time Closing Time
What I need to do is delete many rows in the raw data because they reflect a condition I do not want in my analysis, which is having multiple open trades of a single currency pair during the same time period.
To perform my analysis I need to delete all trades (rows) which meet the above condition.
The worksheet provides examples, along with a rudimentary tool I've used to help me sort/delete rows manually.
Since I am looking at thousands of trades, a manual sort/delete is impractical. (my eyes don't like this kind of work!)
The rudimentary tool I've used in manual sorts/deletes is simply to have a column which returns a "1" if a trade opens before an older trade has closed. I do this by first sorting the data by (a) currency pair then (b) date opened. Then I must manually go through multiple iterations of deleting trades until there all of the "1's" have disappeared.
I'm hoping someone can show me a macro which might do this sorting/testing/deleting automatically, at least to the point where all I have to do is repeatedly press a "macro" button until there are no "1's"
