Consulting

Results 1 to 9 of 9

Thread: Solved: deleting a range based on cell value on another sheet

  1. #1

    Solved: deleting a range based on cell value on another sheet

    Hello, I really need some help on a thing.


    I have a all-data sheet called btc and a temporary storage data sheet called btc trans.

    On the main sheet, I need to select a range of cells within a row, say b:h, if the value on Bi is the same as the A1 value on the sheet called btc trans.
    When selected, I need to clear the values.
    And do it until there is no more Bi's with the same valeu as the A1 value of the btc trans sheet.

    Was I clear?

    Thanks a lot in advance!!

  2. #2
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    I think we need to see a sample workbook
    Peace of mind is found in some of the strangest places.

  3. #3
    here it is

  4. #4

  5. #5
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    [VBA]Option Explicit
    Sub Macro1()
    Dim btc As Worksheet
    Set btc = Sheets(" BTC")
    With Sheets("trans btcs")
    .Range("Z1") = "Cliente"
    .Range("Z2") = .Range("A1").Value & "*"
    btc.Columns("B:B").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    .Range("Z1:Z2"), Unique:=False
    Intersect(btc.UsedRange, btc.Range("A:M")).Offset(1).SpecialCells(xlCellTypeVisible).Clear
    .Range("Z1").Resize(2).Clear
    End With
    btc.ShowAllData
    End Sub
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    Oh my god! that worked great! I guess that I wrote something wrong though,
    I wanted not to clear, but to delete that filerange, moving cells up...
    can you help me with that?

  7. #7
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Change this line
    [VBA]Intersect(btc.UsedRange, btc.Range("A:M")).Offset(1).SpecialCells(xlCellTypeVisible).Delete xlUp[/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  8. #8
    Perfect! thanks a lot!

  9. #9
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    A couple of suggestions
    Avoid merged cells. They cause problems with VBA coding.
    Do not precede sheet names with a Space
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

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