Home |
Search |
Today's Posts |
#1
|
|||
|
|||
dont need anymore these information
Hi all and thanx again for sharing your great knowledge with others,
Im looking to do the following it should be as a macro in VB on a sheet which is protected and with a password, if say AND(D1 is less then zero (0) delete contents from entire row, D2 less then zero delete contents from entire row, and same on D3 and so on, so the macro will search the entire sheet in column D, if cell in D column is less then 0 delete contents from entire row. Thanx again |
#2
|
|||
|
|||
This will remove the entire row when the cell being checked is less than zero
until the cell is found to be empty. Is this what you want or do you just want the row to be blanked? Range("D1").Select Do While Not IsEmpty(ActiveCell) If ActiveCell < 0 Then Selection.EntireRow.Delete End If ActiveCell.Offset(1, 0).Select Loop "Kel" wrote: Hi all and thanx again for sharing your great knowledge with others, Im looking to do the following it should be as a macro in VB on a sheet which is protected and with a password, if say AND(D1 is less then zero (0) delete contents from entire row, D2 less then zero delete contents from entire row, and same on D3 and so on, so the macro will search the entire sheet in column D, if cell in D column is less then 0 delete contents from entire row. Thanx again |
#3
|
|||
|
|||
I think you will have a problem if there are two consecutive negative
numbers. Once the entire row is deleted, Excel will move to the next line and your code will then move to the next line, causing a row to be skipped. Maybe find the last row containing data and work from the bottom up. I usually set a range variable for the rows I want deleted, then delete all of them at the end of the code (another option) Sub DeleteNegatives() Dim x As Object Dim RangeToDelete As Range For Each x In Application.Intersect(Columns(4).EntireColumn, _ ActiveSheet.UsedRange) If x.Value < 0 Then If RangeToDelete Is Nothing Then Set RangeToDelete = x Else: Set RangeToDelete = Union(RangeToDelete, x) End If End If Next x If Not RangeToDelete Is Nothing Then _ RangeToDelete.EntireRow.Delete End Sub "bigwheel" wrote: This will remove the entire row when the cell being checked is less than zero until the cell is found to be empty. Is this what you want or do you just want the row to be blanked? Range("D1").Select Do While Not IsEmpty(ActiveCell) If ActiveCell < 0 Then Selection.EntireRow.Delete End If ActiveCell.Offset(1, 0).Select Loop "Kel" wrote: Hi all and thanx again for sharing your great knowledge with others, Im looking to do the following it should be as a macro in VB on a sheet which is protected and with a password, if say AND(D1 is less then zero (0) delete contents from entire row, D2 less then zero delete contents from entire row, and same on D3 and so on, so the macro will search the entire sheet in column D, if cell in D column is less then 0 delete contents from entire row. Thanx again |
#4
|
|||
|
|||
Whatever you go with, you will need code at the beginning to unprotect the
sheet, then code at the end to protect the sheet again (or you could do these steps yourself before and after running the macro). ActiveSheet.Unprotect Password:="password" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True, Password := "password" "Kel" wrote: Hi all and thanx again for sharing your great knowledge with others, Im looking to do the following it should be as a macro in VB on a sheet which is protected and with a password, if say AND(D1 is less then zero (0) delete contents from entire row, D2 less then zero delete contents from entire row, and same on D3 and so on, so the macro will search the entire sheet in column D, if cell in D column is less then 0 delete contents from entire row. Thanx again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying information from multiple worksheets into one | Excel Discussion (Misc queries) | |||
Update a spreadsheet with new information. | Excel Discussion (Misc queries) | |||
Clearing information in certain columns | Excel Discussion (Misc queries) | |||
Sharing information between Access and Excel | Excel Discussion (Misc queries) | |||
Duplicating Cell Information | Excel Worksheet Functions |