PDA

View Full Version : Solved: deleting a range based on cell value on another sheet



felpslima
10-31-2010, 04:35 PM
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!!

austenr
10-31-2010, 04:52 PM
I think we need to see a sample workbook

felpslima
10-31-2010, 04:59 PM
here it is

jaylotheman
10-31-2010, 05:03 PM
Thanks.

mdmackillop
10-31-2010, 05:29 PM
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

felpslima
10-31-2010, 06:12 PM
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?

mdmackillop
11-01-2010, 01:21 AM
Change this line
Intersect(btc.UsedRange, btc.Range("A:M")).Offset(1).SpecialCells(xlCellTypeVisible).Delete xlUp

felpslima
11-01-2010, 02:22 AM
Perfect! thanks a lot!

mdmackillop
11-01-2010, 06:31 AM
A couple of suggestions
Avoid merged cells. They cause problems with VBA coding.
Do not precede sheet names with a Space