Useing If to Delete a Range

  • Thread starter Thread starter ytayta555
  • Start date Start date
Y

ytayta555

HY ALL !
My big problem is the next : I work in a whole
worksheet ,[ from cell 91 to cell 65536 ] ; I have need a
macro which to ClearContents of Range ,,C91:BB91,,
if the value of cell BD91 < = 32 ; then next row , the
same problem : if BD92 < = 32 , then , to delete ,,the Range" :
,,C92:BB92,, ; then , next row , and so on , from Row 91 to Row
65536 . Please very much to help me !


The macro can look like this [ I write this for pattern ] ,
but how can I modify it ?



Sub Macrocomanda3()

Dim Row As Integer, Col As Integer

For Col = 1 To 4
For Row = 1 To 10
If ActiveSheet.Cells(Row, Col) = 1 Then
ActiveSheet.Cells(Row, Col).ClearContents
Next Row
Next Col

End Sub


RESPECTFULLY TO ALL !
 
work from the BOTTOM up. Something like

mc="bd"
for i = cells(rows.count,mc).end(xlup).row to 2 step -1
if cells(i,mc)<=32 then range(cells(i,"c"),cells(i,"bb")).clearcontents
next i
 
Two problems. You did not have an End If andyou should not use Row which is
a reserved word. Prefixing your variables to indicate the type of variable
gets over the problem of using reserved words.

Try this:-

Sub Macrocomanda3()

Dim intRow As Integer, intCol As Integer

For intCol = 1 To 4
For intRow = 1 To 10
If ActiveSheet.Cells(intRow, intCol) = 1 Then
ActiveSheet.Cells(intRow, intCol).ClearContents
End If
Next intRow
Next intCol

End Sub
 
Hi again,

You might be better not using integers for the row numbers because integers
only work with numbers in the range -32,768 to 32,767.

Try this instead:-

Sub Macrocomanda3()

Dim lngRow As Long, lngCol As Long

For lngCol = 1 To 4
For lngRow = 1 To 10
If ActiveSheet.Cells(lngRow, lngCol) = 1 Then
ActiveSheet.Cells(lngRow, lngCol).ClearContents
End If
Next lngRow
Next lngCol

End Sub


--
Regards,

OssieMac


OssieMac said:
Two problems. You did not have an End If andyou should not use Row which is
a reserved word. Prefixing your variables to indicate the type of variable
gets over the problem of using reserved words.

Try this:-

Sub Macrocomanda3()

Dim intRow As Integer, intCol As Integer

For intCol = 1 To 4
For intRow = 1 To 10
If ActiveSheet.Cells(intRow, intCol) = 1 Then
ActiveSheet.Cells(intRow, intCol).ClearContents
End If
Next intRow
Next intCol

End Sub


--
Regards,

OssieMac


ytayta555 said:
HY ALL !
My big problem is the next : I work in a whole
worksheet ,[ from cell 91 to cell 65536 ] ; I have need a
macro which to ClearContents of Range ,,C91:BB91,,
if the value of cell BD91 < = 32 ; then next row , the
same problem : if BD92 < = 32 , then , to delete ,,the Range" :
,,C92:BB92,, ; then , next row , and so on , from Row 91 to Row
65536 . Please very much to help me !


The macro can look like this [ I write this for pattern ] ,
but how can I modify it ?



Sub Macrocomanda3()

Dim Row As Integer, Col As Integer

For Col = 1 To 4
For Row = 1 To 10
If ActiveSheet.Cells(Row, Col) = 1 Then
ActiveSheet.Cells(Row, Col).ClearContents
Next Row
Next Col

End Sub


RESPECTFULLY TO ALL !
Click to expand...
Click to expand...
 
Thank you very much , now I am trying
to work with your sugestions ; I said that
the macro is for pattern , but my range to delete
[if BD91(X) , < = 32 ], is C919(x):BB91(x);
thank you again for your amability .

DDE
 
Hi Don,

You only need to work backwards from the bottom if deleting rows. With
Clearcontents, it does not matter. I think that the OP's main problem was as
stated "My big problem is the next " because the End If had been omitted.

--
Regards,

OssieMac


Don Guillett said:
work from the BOTTOM up. Something like

mc="bd"
for i = cells(rows.count,mc).end(xlup).row to 2 step -1
if cells(i,mc)<=32 then range(cells(i,"c"),cells(i,"bb")).clearcontents
next i

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
ytayta555 said:
HY ALL !
My big problem is the next : I work in a whole
worksheet ,[ from cell 91 to cell 65536 ] ; I have need a
macro which to ClearContents of Range ,,C91:BB91,,
if the value of cell BD91 < = 32 ; then next row , the
same problem : if BD92 < = 32 , then , to delete ,,the Range" :
,,C92:BB92,, ; then , next row , and so on , from Row 91 to Row
65536 . Please very much to help me !


The macro can look like this [ I write this for pattern ] ,
but how can I modify it ?



Sub Macrocomanda3()

Dim Row As Integer, Col As Integer

For Col = 1 To 4
For Row = 1 To 10
If ActiveSheet.Cells(Row, Col) = 1 Then
ActiveSheet.Cells(Row, Col).ClearContents
Next Row
Next Col

End Sub


RESPECTFULLY TO ALL !
Click to expand...
Click to expand...
 
Hi Don,

You only need to work backwards from the bottom if deleting rows. With
Clearcontents, it does not matter. I think that the OP's main problem was as
stated "My big problem is the next " because the End If had been omitted.

--
Regards,

OssieMac



Don Guillett said:
work from the BOTTOM up. Something like
Click to expand...
mc="bd"
for i = cells(rows.count,mc).end(xlup).row to 2 step -1
if cells(i,mc)<=32 then range(cells(i,"c"),cells(i,"bb")).clearcontents
next i
Click to expand...
Click to expand...




1= I must declare the Range ,,myRange" C91:BB91 and i don't know how;
2=then I must do something like this : For myRange = C91:BB91 To
C65536:BB65536
3=I must set Column BD {mean56} , in this macro ;
3=I think I must put 1 Next : ,,Next lngRow"
and the macro with this settings can look so ..(I think..) :


Dim lngRow As Long, lngCol As Long

lngCol = "BD"

For lngRow = 91 To 65536
If ActiveSheet.Cells(lngRow, lngCol) )<=32 then
range(cells(myRange)),clearcontents

End If
Next lngRow

End Sub

This macro must understand something like : if BD91 <=32 then
EntireRow.ClearContents...BUT I
have need to clear only range C91:BB91 of the Row of active cell ;
I specificate that the initial macro is for pattern ;
please help me with this new sugestion .

Respectfully to All
 
Hi,
Try this code:

Sub Clear_Ranges()

' Macro recorded 3/5/2008 by Tim

Dim cell As Range, rng As Range
Set rng = Range(Cells(1, "BD"), Cells(Rows.Count, "BD").End(xlUp))
For Each cell In rng
If cell.Value < 32 Then
Cells(cell.Row, "C").Select
Selection.Resize(1, 52).Select
Selection.ClearContents
End If
Next
End Sub

Regards,
Tim




ytayta555 said:
Hi Don,

You only need to work backwards from the bottom if deleting rows. With
Clearcontents, it does not matter. I think that the OP's main problem was as
stated "My big problem is the next " because the End If had been omitted.

--
Regards,

OssieMac



Don Guillett said:
work from the BOTTOM up. Something like
Click to expand...
mc="bd"
for i = cells(rows.count,mc).end(xlup).row to 2 step -1
if cells(i,mc)<=32 then range(cells(i,"c"),cells(i,"bb")).clearcontents
next i
Click to expand...
Click to expand...




1= I must declare the Range ,,myRange" C91:BB91 and i don't know how;
2=then I must do something like this : For myRange = C91:BB91 To
C65536:BB65536
3=I must set Column BD {mean56} , in this macro ;
3=I think I must put 1 Next : ,,Next lngRow"
and the macro with this settings can look so ..(I think..) :


Dim lngRow As Long, lngCol As Long

lngCol = "BD"

For lngRow = 91 To 65536
If ActiveSheet.Cells(lngRow, lngCol) )<=32 then
range(cells(myRange)),clearcontents

End If
Next lngRow

End Sub

This macro must understand something like : if BD91 <=32 then
EntireRow.ClearContents...BUT I
have need to clear only range C91:BB91 of the Row of active cell ;
I specificate that the initial macro is for pattern ;
please help me with this new sugestion .

Respectfully to All
Click to expand...
 
Correct, it doesn't matter but I do it out of habit because sometimes clear
and sometimes delete. So, I think OP can use my original code by changing to
2 so that it is now to 91, like this leaving columns A:B and bc>> alone.
If desired, change to
for i = 91 to cells(rows.count,mc).end(xlup).row


sub clearmycontents()
mc="bd"
for i = cells(rows.count,mc).end(xlup).row to 91 step -1
if cells(i,mc)<=32 then range(cells(i,"c"),cells(i,"bb")).clearcontents
next i
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
ytayta555 said:
Hi Don,

You only need to work backwards from the bottom if deleting rows. With
Clearcontents, it does not matter. I think that the OP's main problem was
as
stated "My big problem is the next " because the End If had been omitted.

--
Regards,

OssieMac



Don Guillett said:
work from the BOTTOM up. Something like
Click to expand...
mc="bd"
for i = cells(rows.count,mc).end(xlup).row to 2 step -1
if cells(i,mc)<=32 then range(cells(i,"c"),cells(i,"bb")).clearcontents
next i
Click to expand...
Click to expand...




1= I must declare the Range ,,myRange" C91:BB91 and i don't know how;
2=then I must do something like this : For myRange = C91:BB91 To
C65536:BB65536
3=I must set Column BD {mean56} , in this macro ;
3=I think I must put 1 Next : ,,Next lngRow"
and the macro with this settings can look so ..(I think..) :


Dim lngRow As Long, lngCol As Long

lngCol = "BD"

For lngRow = 91 To 65536
If ActiveSheet.Cells(lngRow, lngCol) )<=32 then
range(cells(myRange)),clearcontents

End If
Next lngRow

End Sub

This macro must understand something like : if BD91 <=32 then
EntireRow.ClearContents...BUT I
have need to clear only range C91:BB91 of the Row of active cell ;
I specificate that the initial macro is for pattern ;
please help me with this new sugestion .

Respectfully to All
Click to expand...
 
HI ALL

I tried Tim's Macro and it work perfect , that's what
I needed ; excuse me because I wasn't clear , {and
here you must be so if you want to get the right ad-
vice ; }

I've done only 2 changes in Tim's Macro [for my particular need] :

in second line of macro :Set rng = Range(Cells(1, "BD"),
Cells(Rows.Count, "BD").End(xlUp)) ,
I must changed so : Set rng = Range(Cells(91, "BD"),
Cells(Rows.Count, "BD").End(xlUp));
and the 4-th line of macro :If cell.Value < 32 Then
becomed : If cell.Value <= 32 Then

ALL YOUR ADVICE BUILT THIS MACRO !! THIS CASE IS RESOLVED !
Thank you to ALL . YOU ARE ALL WONTHERFULL

{[ps: I'll post soon a new subiect very strong and very hard..I
think... , but very usefull !!! and intersting !!!
I have a solution , but it isn't the complete solution ! Then , I
shall give my solution which I have
for the subiect I'll post soon ; I was looking for this solution
(which I have for my next subiect I'll post)
some months ; I think here shall be the time of teachers ]}

Respectfully again ,
soon
 
Back
Top