about Used range from particular cell

[VBABEGINER]

Hello All board member,
It seems from very very long time on board..Happy to questioning..

Ok..

I have question like, after my c10 range, whatever be the rows data are there, will be clear..

Can anyone help me on this..

 

[Rick Rothstein]

I have question like, after my c10 range, whatever be the rows data are there, will be clear..

Can anyone help me on this..

Your question is not entirely clear, but let me take a guess. You want to clear all the cells which, in some way touch cell C10 or another cell that indirectly touches cell C10 through a chain of such cells. That is called the CurrentRegion and you would clear is like this...

Range("C10").CurrentRegion.Clear

You could use ClearContents instead of Clear if you wanted to leave existing formats intact.

 

[Joe4]

If you want to leave everything above row 10 intact, but delete everything below that, try:

    Dim lastCell As Range
    Set lastCell = ActiveCell.SpecialCells(xlLastCell)
    Range("C10:" & lastCell.Address).EntireRow.ClearContents

If you want to leave columns A and B intact, in addition to everything above row 10, then just change the last row in the code above to:

    Dim lastCell As Range
    Set lastCell = ActiveCell.SpecialCells(xlLastCell)
    Range("C10:" & lastCell.Address).ClearContents

 

[VBABEGINER]

Thank You Rick..for your reply..

 

[VBABEGINER]

Thanks Joe for your reply...1st one works...

 

[VBABEGINER]

If you want to leave everything above row 10 intact, but delete everything below that, try:

    Dim lastCell As Range
    Set lastCell = ActiveCell.SpecialCells(xlLastCell)
    Range("C10:" & lastCell.Address).EntireRow.ClearContents

If you want to leave columns A and B intact, in addition to everything above row 10, then just change the last row in the code above to:

    Dim lastCell As Range
    Set lastCell = ActiveCell.SpecialCells(xlLastCell)
    Range("C10:" & lastCell.Address).ClearContents

Hi Joe, a problem is that, some scenario...my C last row will be changing. so instead of c i have now taken Col A.
so i take last row of my Col A. And there be a chances like, from Col C to Col K...rows may be multiple.
hence i edit code like this..

Dim LstRw3 As Integer
LstRw3 = Range("A" & Rows.Count).End(xlUp).Row + 1

'If you want to leave everything above row 10 intact, but delete everything below that, try:


Dim lastCell As Range
Set lastCell = ActiveCell.SpecialCells(xlLastCell)
'Range("C10:" & lastCell.Address).EntireRow.ClearContents


Range("A:" LstRw3 & lastCell.Address).EntireRow.ClearContents

but this not working...
could you pls help in this..

 

[Joe4]

You have not built your address properly.
Addresses look like this A1:C10 (column reference, row reference, colon, column reference, row reference).

Try:

[COLOR=#333333][I]Range("A" & LstRw3 & ":" & lastCell.Address).EntireRow.ClearContents[/I][/COLOR]

 

[VBABEGINER]

Excellent...This works perfect...

Can you also tell me, suppose if only data clear from A2 range...No clearance of headings..

You have not built your address properly.
Addresses look like this A1:C10 (column reference, row reference, colon, column reference, row reference).

Try:

[COLOR=#333333][I]Range("A" & LstRw3 & ":" & lastCell.Address).EntireRow.ClearContents[/I][/COLOR]

 

[Joe4]

Do you mean what if you want to delete everything except for headers?
Then just hard-code in your starting address, i.e.

[COLOR=#333333]Range("A2:" & lastCell.Address).EntireRow.ClearContents[/COLOR]

 

[VBABEGINER]

This to working..

Rows("2:" & Rows.Count).ClearContents