I'm having some problem with a part of my code that deletes entire columns. With ActiveCell as my reference, I want to delete 'Abs_Diff' Columns to the left hand side of the ActiveCell column, including the ActiveCell column. 'Abs_Diff' is a variable of integer type.
SO if ActiveCell column is "P" and 'Abs_Diff'=2, then delete Columns "O:P"
if ActiveCell column is "P" and 'Abs_Diff'=4, then delete Columns "M:P"
My code is selecting columns incorrectly, maybe due to merged cells in cols A,B,C or something..not sure.
Here is my code:
Code:
'DELETE COLUMNS
Set StartPoint = ActiveCell
For X = 1 To Abs_Diff
StartPoint.EntireColumn.Delete
Set StartPoint = ActiveCell.Offset(0, -1)
Next X
I have Column Headers in Row 1 (except column A), What I need to do is write a Macro or some code to
1.Search the entire row 1 for column headers containing the suffix _CSV and delete the entire columns. 2.Shift all remaining columns to the left so no blank columns exist. (There is a time stamp in row A that I wish to keep where it is.)
Next I would like a separate routine to sort the columns in the order of a Pre-defined list that I specify in a range. For example the list of column headers reside in the Range (A1:A200)
I need excel to delete entire rows if there are duplicates in two columns combined.
So, if I have this sample data (in reality I have a lot more columns)
1 Tr. ID Last Name First Name Sex 2 RBM_A Miller Johnny M 3 RBM_A Bayron Josephine F 4 RBM_A Jackson Reynaldo M
[Code] ........
I want excel to delete only row 9, not 11, 13 or 14.
So far, if I look for duplicates in both columns it marks duplicates in either one. Also if I delete the duplicates, it deletes them only in those two columns, leaving me with distorted data (in this example, row 14 would have no last or first name, but only the values "RBM_C" in column B and "F" in column E.
I'm working on Outlook 2003 and Word 2000 which are not compatible. I have an Excel Sheet which are my contacts from Outlook and I want to re-work it to have only a certain category left.
I need a macro that does - search a certain column - deletes the row if it does NOT find a certain condition
Ideal would be a little pop up window to enter which category I want left.
I written one code to delete Entirerow if value match. It's working fine .the code delete all match except one match.!
------------------------------------------------- Option Compare Text Sub delete_duplicate() Cells(Rows.Count, 1).End(xlUp).Select Range(ActiveCell, Range("A1")).Select For Each cell In Selection If cell.Value = "Already updated" Then cell.EntireRow.Delete End If Next End Sub -----------------------------------
If column B contains the word FALSE, I need to delete that entire row, then I need to repeat this action on 11 sheets out of 14 on one workbook, in one action.
i have over around 1500 pfolios in Col A, i need macro that will delete entire row which has the highlighted pfolios. Now i tried advanced filter which would work but the date in Col A6 will change every month, so dont want to go down that road, is there alternative option other than advanced filter ..
I want to know if there is a way to delete an entire row from an array? I have a 2 dimensional array and I want to loop through the elements in the first column and delete an entire row based on some conditions. Since the number of rows are too many usual looping takes a long time so I want to use an array.
I've a worksheet containing a list of items some of which need deleting. These are identified from a particular range where users input "y" to notifiy the record can be removed. The code below works just fine if all I do is Clear the cells containing "y". However using: Range(rCell.Address).EntireRow.Delete
the macro stops after deleting the first item. It suddenly believes there aren't any further items to delete and Ends. There are no error messages returned. Entire macros is below.
Sub unreg_report() Dim rRange As Range, rCell As Range Set rRange = Range("unreg_list").SpecialCells _ (xlCellTypeConstants, xlTextValues) For Each rCell In rRange If rCell = "y" Then Range(rCell.Address).EntireRow.Delete
I have two worksheet. One worksheet (ws1) contains a list of item I want. The other sheet (ws2) contains multiple columns where the header (row 6) is named by item name.
I have the following code which deletes the entire column if the header name is not in the list contained in ws1 :
VB: Sub delete_col() Dim wanted As Boolean Set ws1 = Workbooks("test1").Sheets("aaa")
[Code]....
First of all, this loop does not work properly since deleting the entire column shift them on the left, so when I first analyze column 11, if I delete it and then analyze column 12, the real column 12 now became column 11 and so on...
Secondly, this code is pretty slow. I am pretty sure I don't have to loop through my initial item list everytime I do Instr on a new column.
IF Column C doesn't have data in entire column then delete C D E F IF Column D doesn't have data in entire column then delete D E F IF Column E doesn't have data in entire column then delete E F IF Column F doesn't have data in entire column then delete F
Same way for heading NN's
IF Column G doesn't have data in entire column then delete G H I J K L M N IF Column H doesn't have data in entire column then delete H I J K L M N IF Column I doesn't have data in entire column then delete I J K L M N IF Column J doesn't have data in entire column then delete J K L M N IF Column K doesn't have data in entire column then delete K L M N IF Column L doesn't have data in entire column then delete L M N IF Column M doesn't have data in entire column then delete M N IF Column N doesn't have data in entire column then delete N
I have fixed headers on row 16, from columns A-AC.
I want to be able to delete the entire column, if the row has a certain string, such as "Chart ID" .
I also want to expand it to include other strings such as "Month" and "Source" . So if it contains any of these words, the columns should be deleted. It should be an exact match (as other headers contain the word "month").
I have a worksheet where I have restricted the users from scrolling around beyond a certain range.
Because of that the user is prevented from selecting an entire row. There are some cases where the user should be able to delete entire row or range of rows.
How can I allow them to select say cells "C17:C35" [they would just hoghlight the range] and hit ctrl+d; which would trigger a macro and the macro in turn would delete all the rows in that selection [rows 17 through 35]?
I know some basic codes for deleting entire rows, but I don't know how to read first and last row of the selection under Target property.
I need to delete entire row of the result of autofiltering i.e. the visible rows only
If I do this manualy it works but if I record this on a macro, it records the absolute cells range I pick, when in fact the result of the filtering is different every time.
Is there a way around this with VBA?
I've Seen Copy paste in this forum but in my case i need to delete the entire row.
I can't get this code to work which I want to delete the entire row if the cell in the specified range contains only 3 characters;
Code:
Sub CharCount() Dim cell As Range Dim bottomK As Integer bottomK = Range("D" & Rows.Count).End(xlUp).Row Dim rng As Range Set rng = Range("D2:D" & bottomK) For Each cell In rng If Len(cell) = 3 Then EntireRow(bottomK).Delete End If Next cell End Sub
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
