Macro Commands

  • Thread starter Thread starter Chris Premo
  • Start date Start date
C

Chris Premo

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?





--
 
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
 
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
 
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
 
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
 
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



--
 
Chris Premo said:
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.
Click to expand...

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
 
Back
Top