Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using selected ranges
Hello,
Im tryin to get a sub or macro that will search a selection(in column) and if the individual cells meet a critieria I want to delet the rows. The part Im havin trouble with is working with the selecte area. I wrote this one which works if I select the cells individually: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 2/25/04 by Enter Your Name Here ' Dim A As String A = ActiveCell If A = "FEES" Then Selection.Delete Shift:=xlUp End If End Sub What do I do to make it work with a selection -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using selected ranges
Sub Macro1()
' ' Macro1 Macro ' Macro recorded 2/25/04 by Enter Your Name Here ' Dim A As String, first as Long, last as Long if selection.Areas.Count 1 or selection.Columns.count 1 then msgbox "Select single column only, 1 area" exit sub End if first = selection(1).Row last = selection(selection.count).Row for i = last to first step -1 If cells(i,selection.column).Value = "FEES" Then cells(i,Selection.Column).Delete Shift:=xlUp End If Next End Sub -- Regards, Tom Ogilvy "ksnapp " wrote in message ... Hello, Im tryin to get a sub or macro that will search a selection(in a column) and if the individual cells meet a critieria I want to delete the rows. The part Im havin trouble with is working with the selected area. I wrote this one which works if I select the cells individually: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 2/25/04 by Enter Your Name Here ' Dim A As String A = ActiveCell If A = "FEES" Then Selection.Delete Shift:=xlUp End If End Sub What do I do to make it work with a selection? --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using selected ranges
Hello,
This does the trick Sub DeleteEntireRowWithSpecialValue() Dim rngFirst As Range Dim rngLast As Range ' ' We step throu the selection one row below ' the row that will be tested for deletion ' otherwise we loose the control of rngFirst Set rngFirst = Selection.Cells(1).Offset(1, 0) Set rngLast = Selection.Cells(Selection.Cells.Count).Offset(1, 0) If rngFirst.Row = rngLast.Row Or rngFirst.Column < rngLast.Column Then MsgBox "Use a series of rows, one column width" Else Do If rngFirst.Offset(-1, 0).Value = "FEES" Then rngFirst.Offset(-1, 0).EntireRow.Delete Else Set rngFirst = rngFirst.Offset(1, 0) End If Loop Until rngFirst.Row = rngLast.Row End If End Sub Wouter Magre. ksnapp wrote in message ... Hello, Im tryin to get a sub or macro that will search a selection(in a column) and if the individual cells meet a critieria I want to delete the rows. The part Im havin trouble with is working with the selected area. I wrote this one which works if I select the cells individually: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 2/25/04 by Enter Your Name Here ' Dim A As String A = ActiveCell If A = "FEES" Then Selection.Delete Shift:=xlUp End If End Sub What do I do to make it work with a selection? --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using selected ranges
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple cells or columns are selected instead of selected cell or | Excel Discussion (Misc queries) | |||
Allowing users to edit selected ranges only | Excel Discussion (Misc queries) | |||
Cells are selected but aren't displayed as selected | Excel Discussion (Misc queries) | |||
Cells are selected but aren't displayed as selected | Excel Worksheet Functions | |||
how do i make it so that when a sheat is selected either via link or tab, that xlLastCell is selected. the last on the sheet. | Excel Worksheet Functions |