Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Commands
I want to be able to search through an imported text file for the first
instance of a work (no problem doing this) and then select all the "Row" above it and delete them. So . . . How do I programatically determine which row the "Search" string is in and then pass that to the next line to delete all rows above that row? -- |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Commands
Say we are searching for happicess:
Sub Macro1() Range("A1").Select Cells.Find(What:="happiness", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Range("A1:A" & ActiveCell.Row - 1).EntireRow.Delete End Sub -- Gary''s Student - gsnu200822 "Chris Premo" wrote: I want to be able to search through an imported text file for the first instance of a work (no problem doing this) and then select all the "Row" above it and delete them. So . . . How do I programatically determine which row the "Search" string is in and then pass that to the next line to delete all rows above that row? -- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Commands
Sub Test()
Dim R As Range, W As String W = "Boat" ' < searchword Set R = Cells.Find(W) Rows("1:" & R.Row - 1).Delete End Sub HTH. Best wishes Harald "Chris Premo" wrote in message ... I want to be able to search through an imported text file for the first instance of a work (no problem doing this) and then select all the "Row" above it and delete them. So . . . How do I programatically determine which row the "Search" string is in and then pass that to the next line to delete all rows above that row? -- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Commands
hi
here is a sniple to illistrate how it might be done. Sub rowfind() Dim r As Long range("A10").select 'simulates your find r = ActiveCell.Row Rows("2:" & r - 1).EntireRow.Delete 'assumes a header row End Sub regards FSt1 "Chris Premo" wrote: I want to be able to search through an imported text file for the first instance of a work (no problem doing this) and then select all the "Row" above it and delete them. So . . . How do I programatically determine which row the "Search" string is in and then pass that to the next line to delete all rows above that row? -- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Commands
Option Explicit
Sub testme() Dim FoundCell As Range Dim myWord As String myWord = "asdf" With ActiveSheet Set FoundCell = .Cells.Find(what:=myWord, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox myWord & " not found!" Else If FoundCell.Row = 1 Then MsgBox "what should happen here?" Else .Range("a1").Resize(FoundCell.Row - 1, 1).EntireRow.Delete End If End If End With End Sub Chris Premo wrote: I want to be able to search through an imported text file for the first instance of a work (no problem doing this) and then select all the "Row" above it and delete them. So . . . How do I programatically determine which row the "Search" string is in and then pass that to the next line to delete all rows above that row? -- -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Commands
For those who answered, thanks I figured it out just before you posted
your answers. For example, this is how: ActRow = ActiveCell.Row Range("A" & ActRow).Select or if I needed to go up (or down) a row: ActRow = ActiveCell.Row + 1 Rows("1:" & ActRow).Select And if I needed to find two points I use this ActiveCell.Select ActRow = ActiveCell.Row use a search to find the next point and ActiveCell.Select ActRow2 = ActiveCell.Row - 1 Range("B" & ActRow & ":C" & ActRow2).Select ************************************************** ******** Now I have another issue. I'm saving my data as a Text file and use this command. Unfortunately, it prompts for a use action to save/replace the file and then prompts again when I close the file. I'd like code that would not require use intervention. ActiveWorkbook.SaveAs Filename:="I:\Configs\" & TextStr2, FileFormat:= xlText, CreateBackup:=False ActiveWorkbook.Close -- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Commands
"Chris Premo" wrote in message ... Now I have another issue. I'm saving my data as a Text file and use this command. Unfortunately, it prompts for a use action to save/replace the file and then prompts again when I close the file. I'd like code that would not require use intervention. Try Application.Displayalerts = False ' meaning I know what i'm doing, don't ask, then ' your save code here, then Application.Displayalerts = True HTH. Best wishes Harald |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where do I find the commands for a macro | New Users to Excel | |||
How can i memorise the Macro Commands? | Excel Discussion (Misc queries) | |||
How do I call MS DOS commands from Macro. | Excel Discussion (Misc queries) | |||
Macro Commands | Excel Discussion (Misc queries) | |||
macro commands | Excel Discussion (Misc queries) |