Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
preset range
I have the following Module:
Sub DelEmptyRow() Rng = Selection.Rows.Count ActiveCell.Offset(0, 0).Select Application.ScreenUpdating = False For i = 1 To Rng If ActiveCell.Value = 0 Then 'You can replace "" with 0 to delete rows with 'the value zero Selection.EntireRow.Delete ElseIf ActiveCell.Value = "Company" Then Selection.EntireRow.Delete ElseIf ActiveCell.Value = "Controlling Area :" Then Selection.EntireRow.Delete ElseIf ActiveCell.Value = "Proj. number" Then Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Next i End Sub What can I add so I don't have to select the data and use a pre determined range of "A8:Z300" as the selection set. Also is there a way to make the code smaller? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
preset range
This is one alternative:
Sub DelEmptyRow() Dim Rng As Range, sRng As Range, i As Long, col As Long col = ActiveCell.Column Set Rng = Range(ActiveCell.Address & ":" & _ ActiveSheet.Cells(Rows.Count, col). _ End(xlUp).Address) For i = Rng.Rows.Count To 1 Step -1 Set sRng = ActiveSheet.Cells(i, col) If sRng = "Company" Or sRng = "Controlling Area :" Or _ sRng = "Proj. number" Then sRng.EntireRow.Delete End If Next End Sub "Novice Lee" wrote: I have the following Module: Sub DelEmptyRow() Rng = Selection.Rows.Count ActiveCell.Offset(0, 0).Select Application.ScreenUpdating = False For i = 1 To Rng If ActiveCell.Value = 0 Then 'You can replace "" with 0 to delete rows with 'the value zero Selection.EntireRow.Delete ElseIf ActiveCell.Value = "Company" Then Selection.EntireRow.Delete ElseIf ActiveCell.Value = "Controlling Area :" Then Selection.EntireRow.Delete ElseIf ActiveCell.Value = "Proj. number" Then Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Next i End Sub What can I add so I don't have to select the data and use a pre determined range of "A8:Z300" as the selection set. Also is there a way to make the code smaller? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
preset range
Are those three text strings you are testing for all located in the same
column (if so, which one) or can the be located in any of the columns within the range A8:Z300? -- Rick (MVP - Excel) "Novice Lee" wrote in message ... I have the following Module: Sub DelEmptyRow() Rng = Selection.Rows.Count ActiveCell.Offset(0, 0).Select Application.ScreenUpdating = False For i = 1 To Rng If ActiveCell.Value = 0 Then 'You can replace "" with 0 to delete rows with 'the value zero Selection.EntireRow.Delete ElseIf ActiveCell.Value = "Company" Then Selection.EntireRow.Delete ElseIf ActiveCell.Value = "Controlling Area :" Then Selection.EntireRow.Delete ElseIf ActiveCell.Value = "Proj. number" Then Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Next i End Sub What can I add so I don't have to select the data and use a pre determined range of "A8:Z300" as the selection set. Also is there a way to make the code smaller? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
preset range
Rick
Yes they are all in column a "Rick Rothstein" wrote: Are those three text strings you are testing for all located in the same column (if so, which one) or can the be located in any of the columns within the range A8:Z300? -- Rick (MVP - Excel) "Novice Lee" wrote in message ... I have the following Module: Sub DelEmptyRow() Rng = Selection.Rows.Count ActiveCell.Offset(0, 0).Select Application.ScreenUpdating = False For i = 1 To Rng If ActiveCell.Value = 0 Then 'You can replace "" with 0 to delete rows with 'the value zero Selection.EntireRow.Delete ElseIf ActiveCell.Value = "Company" Then Selection.EntireRow.Delete ElseIf ActiveCell.Value = "Controlling Area :" Then Selection.EntireRow.Delete ElseIf ActiveCell.Value = "Proj. number" Then Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Next i End Sub What can I add so I don't have to select the data and use a pre determined range of "A8:Z300" as the selection set. Also is there a way to make the code smaller? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Preset Columns | Excel Discussion (Misc queries) | |||
preset table | Excel Programming | |||
Clear cells to a preset value | Excel Worksheet Functions | |||
Clear cells to a preset value | Excel Worksheet Functions | |||
Is there anyway to add more than the preset 265 columns in Excel? | Excel Discussion (Misc queries) |