Delete Entire Row Based On Selection
May 27, 2009
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.
View 3 Replies
ADVERTISEMENT
Jun 16, 2014
I have table in which against the Column A data values are added in Column B, C and D.
If Column B,C and D cell values is 0, then I want entire row including A to be deleted. How can I do this in VBA?
Note: Column A data begins from Row 15 and below.
Just to avoid confusion. I want row deletion, only when all the three cell on B,C and D is = 0
View 7 Replies
View Related
May 13, 2009
I have a problem deleting rows based on a condition, i didn't know how to use offset method.
i'm using a table like this one :
A B C
1 Main design1 FFR0
2 Extra design2 FFR0
3 Main design3 FFR0
4 Extra design4 FFR0
.
.
n Main designn FFR0
I want to delete entire row if "FFR0" in column C exists in a row beginning by "EXTRA" (column A)
View 6 Replies
View Related
Jul 18, 2012
Ihave a list of names in column A in certain rows instead of name there is an email address in this case I want to delete the entire row
View 4 Replies
View Related
Oct 19, 2011
Simple code that can delete entire row if certain criteria is met in a single cell
Example
I have a bank statement where under a first column (DATA TYPE), the cell could contain either "DATA" or "TOTAL"
How can I delete the rows contain the word "TOTAL" assuming the column is already sorted.
View 3 Replies
View Related
Dec 13, 2013
I have got a vacancy tracker spreadsheet and I need it to move an entire row from the 'Open' to the 'Closed' sheet based on the status in column K, i.e. 'Closed +', 'Closed -', and 'Closed + Achieve'. Once this has been done I want it to delete the entire row in the 'Open' sheet. At the moment it just cuts the entire row and then it is left blank and when I delete it manually the macro stops working completely.
Also I can only get it to move 'Closed +' and 'Closed -' and seem not to be able to add a command to move 'Closed + Achieve' rows.
This is what I have got so far:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("K")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value = "Closed +" Then
[Code] .......
View 2 Replies
View Related
Mar 9, 2007
I used this macro to find the duplicates in column B:
Sub KryDups()
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell. Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell <> ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub
The duplucate cells are now red in color. (RGB(255, 0, 0)). How do I now code VB to delete the rows in column B where the cell color is red? Here is some of the code that I tried:................
View 5 Replies
View Related
Jun 10, 2009
I know it can delete entire rows based on the value of single cell, I just don't know how to do it. So what I need is a macro that will delete an entire row if the value in a particular column = 0.
More details: ....
View 9 Replies
View Related
Oct 14, 2008
Here is my
ThisWorkbook.sheets("sheet1").Range("M").Select
For Each cell In Selection
If cell.Value = ThisWorkbook.Sheets("Sheet2").Range("A").Value Then
cell.EntireRow.Delete
End If
Next cell
Range("a1").Select
End Sub
I want to remove all rows in sheet 1 that contain any value found in Sheet2 A I using XL 2003.
View 9 Replies
View Related
Jun 7, 2012
I have two listboxes where I can move tickers from left to right. This is synced with a sheet, such that the tickers also are pasted there too.
But if I use the remove button, I can remove tickers from the listbox, but I cannet delete them from the sheet. If I understand right the listbox only refer to what row the selected is, not what the text is...
And then its a bit tricky. I have tried a code where it just add the list one more time, after the itmes has been removed. And that could work if it hadn't been for the second column which also has a text, this column is updated after every ticker has been moved from left to right.
What I would like to have is a function that would know what the text in the row that I delete, or remove.
This is what I have so far:
Code:
Private Sub cmdMoveToLeft_Click()
Dim i As Integer, j As Integer, k As Integer
Dim RowsStart As Integer
Dim LastRow As Long
Dim Rows As Integer
RowsStart = Me.ListBoxX.ListCount
[Code] .......
View 4 Replies
View Related
Jun 3, 2013
If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
View 1 Replies
View Related
Jan 30, 2007
I have attached a portion of an excel file I am using at the moment. What I require is a piece of VBA code which will allow me to copy selected data based on a combo box selection. What needs to happen is this :-
If the selection in the "Index" worksheet combo box related to "Month" in cell G19 is for instance "December", I would like the macro to compare this cell value to the cell values in cells C96, C124, C152, C180, C209, C236, C263 and C290 in the "Tech Services" worksheet and where the values match.......copy the commentary (Range C126:Z147 in December's case) to the range C34:Z55.
View 9 Replies
View Related
Jul 20, 2007
I want to have a drop down list in a cell so that the value in the cell can be only selected from two columns of data. Additionally once the data from the first column has been selected I want to be able to limit the inputs the user can select from in the second column.
e.g. In cell C115 I want to have the value BDS05.
I want to be able to select the value BDS from a drop down list of values and once that value has been selected I want to be able select 05 from a list of values from 01 to 14
If I select BCS as the first value then I want the second set of values to be limited to 01 to 02 etc.
I have read about combo boxes and list boxes and I'm a bit confused about the best way to achieve this (or even if I can).
View 6 Replies
View Related
Oct 28, 2008
How would i search the entier worksheet im in, looking at column b only, and delete the entire row if i find the word Date in it.
View 9 Replies
View Related
Sep 24, 2006
when i delete the entirerow, it shifts the rows up (which is what I want) but it skips that row when it does.
so if I have 2 blank cells together it will delete the 1st and leave the 2nd blank.
Sub ()
Dim myrange As Range
Set myrange = ("a:a")
For Each c In myrange
If c.Value = "" Or c.Value <= 5 Then
c.EntireRow.Delete
End If
Next
End Sub
View 5 Replies
View Related
May 3, 2006
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.
View 9 Replies
View Related
Dec 11, 2012
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
-----------------------------------
View 3 Replies
View Related
Aug 19, 2013
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
View 1 Replies
View Related
Mar 17, 2014
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.
View 6 Replies
View Related
Sep 18, 2008
I use the code below to delete the entire row that has the Text "Pc." in it. How can I make this delete the row with "Pc." plus the next row?
Sub PL()
Dim i As Long
For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If InStr(Cells(i, "A").Text, "Pc.") Then
Cells(i, "A").EntireRow.Delete
End If
Next i
End Sub
View 9 Replies
View Related
Feb 17, 2010
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 ..
View 9 Replies
View Related
Apr 26, 2007
I am trying to Lookup multiple values (can't be done manually) in one report (Report 2) from another (Report 1).
If the value from Report 1 isn't present in Report 2, I want the entire row the value is found in (From Report 1) deleted.
View 4 Replies
View Related
Jun 29, 2007
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.
View 9 Replies
View Related
Aug 28, 2007
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
End If
Next rCell
End Sub
View 2 Replies
View Related
Oct 1, 2012
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.
View 9 Replies
View Related
Feb 21, 2013
I have columns
Row 1 is heading..
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
View 1 Replies
View Related
Apr 15, 2014
i want to delete entire row if D2:D10000 has a numeric value e.g .111 to 100000.1114
View 4 Replies
View Related
Apr 16, 2014
i want to delete entire row if Column C2:C1000 are blank
e.g if range c2:c100 have data then delete the rows C101:C1000
View 3 Replies
View Related
May 5, 2014
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").
View 12 Replies
View Related
Jun 15, 2011
I have a file with 238 rows and 10 columns (see file attached) Column G include phone numbers, some phone numbers are duplicated.
How can I use a macro (I must do it with a macro) to delete the ENTIRE row if the value in column G is the same in other row?
For example: look at rows 2 and 3 : the value in column G2 and G3 is the same, therefor delete row 3
(and if the value in G4 was also the same, then delete also row 4, and so on..)
View 10 Replies
View Related
