Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use the row numbers obtained by searching strings to select range in Excel

    I am trying to search strings "Comment1" and "Comment2" that are somewhere in columns A and C respectively, and then delete all rows lie between the row that contains "Comment1" (inclusive) and the row before "Comment2".

    I have written below code but somehow it gets stucked in the last second line where I am trying to select a range using the two row numbers obtained by searching the two strings:

    Code:
    Dim rngSearch1 As Range, rngFound1 As Range, rngSearch2 As Range, rngFound2 As Range, rowComment1 As Integer, rowComment2 As Integer
    Set rngSearch1 = Range("A:A")
    Set rngSearch2 = Range("C:C")
    Set rngFound1 = rngSearch1.Find(What:="Comment1", LookIn:=xlValues, LookAt:=xlPart)
    Set rngFound2 = rngSearch2.Find(What:="Comment2", LookIn:=xlValues, LookAt:=xlPart)
    
    MsgBox "Cell that contains the term Comment1 is in row " & rngFound1.Row
    
    rowComment1 = rngFound1.Row
    
    MsgBox "Cell that contains the term Comment2 is in row " & rngFound2.Row
    
    rowComment2 = rngFound2.Row
    
    Rows("rowComment1:rowComment2 - 1").Select
    Selection.Delete Shift:=xlUp
    When it runs the last second line VBE complains that "Run-time error '13': type mismatch" even though according to the two message boxes the code gives correct row numbers. Am I missing something here? I am picking this is caused by the range object, but how do get this last second line work?

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    You cant perform a mathematical function on a string. Nor can you include a variable inside a string and expect to return the value of the variable. Try this instead...
    Code:
    Sub Test()
      Dim rngSearch1 As Range, rngFound1 As Range
      Dim rngSearch2 As Range, rngFound2 As Range
      Dim rowComment1 As Integer, rowComment2 As Integer
      
      Set rngSearch1 = Range("A:A")
      Set rngSearch2 = Range("C:C")
      
      Set rngFound1 = rngSearch1.Find(What:="Comment1", LookIn:=xlValues, LookAt:=xlPart)
      Set rngFound2 = rngSearch2.Find(What:="Comment2", LookIn:=xlValues, LookAt:=xlPart)
      
      rowComment1 = rngFound1.Row
      rowComment2 = rngFound2.Row
      MsgBox "Comment1 is in row " & rowComment1 & vbCr & _
              "Comment2 is in row " & rowComment2
      
      Rows(rowComment1 & ":" & rowComment2 - 1).Delete
    End Sub
    Andrew Lockton, Chrysalis Design, Melbourne Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •