Clear sheet from row x downwards before loading new data

[davecarrera]

Hi All,

How do i delete current rows in a excel sheet from say row 8 downwards before loading in my ADODB.Recordset from a fresh query

Kind Regards

Dave

 

[Fenrirshowl]

Try

Rows("8:65536").Clearcontents

Fen

 

[davecarrera]

simply question answered simply

Many thanks

Dave

 

[SkipVought]

Hi,

It's often necessary to be more targeted in such an operation.

With TheQuerySheet.[A8].CurrentRegion
   .Range(TheQuerySheet.Cells(1, 1), TheQuerySheet.Cells(.Rows.Count, .Columns.Count)).ClearContents
End With

Skip,

_[red][/red]

 

[Fenrirshowl]

Hi Skip

Your post interests me and I need some enlightenment.

I assume it is designed to select and clear the currentregion to the right and down on A8.

I copied the code, substituting a select command rather than a clear contents (as I am just running an example) and also substituting the sheet name to sheet 1. I ran the following code and found the result to be the same as Cells(1,1).currentregion.select

Sub selekt()
With Sheets("sheet1").[A8].CurrentRegion
    .Range(Sheets("sheet1").Cells(1, 1), Sheets("sheet1").Cells(.Rows.Count, .Columns.Count)).Select
End With
End Sub

I admit that rows 1 to 7 on sheet1 are not blank but I would expect this might be the case with Dave's data. Would this mean that the expected result would be equal to Cells(1,1).currentregion.select?

Finally, I've not seen [A8] used like that - is it simply shorthand for Range("A8") or is it more technical?

Thanks

Fen

 

[SkipVought]

I assumed that there was a row of headings and originally coded it as

With Sheets("sheet1").[A8].CurrentRegion
    .Range(Sheets("sheet1").Cells([red][b]2[/b][/red], 1), Sheets("sheet1").Cells(.Rows.Count, .Columns.Count)).Select
End With

certainly [A8].currentregion.clearcontents does the job.

Skip,

_[red][/red]

 

[Fenrirshowl]

Many thanks

Fen

 

[xlbo]

as long as there are no gaps in the data - then currentregion may fail to include all your data

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Geoff,

Its a query resultset. It should be contiguous.

Skip,

_[red][/red]

 

[PHV]

Why are you thinking that [A8].CurrentRegion.ClearContents will preserve Rows 1 thru 7 ?

 

[xlbo]

Skip - very true - just covering all bases in case anyone else reads this thread and isn't working with a recordset

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

One shoud never have ANY non-table data contiguous to a table. Otherwise, it's not REALLY a table.

Skip,

_[red][/red]

 

[xlbo]

aaaaah - yes - shouldn't have non contiguous data but.........

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

yes, there's often the BUT.....

or

BUTT ;-)

Skip,

_[red][/red]

 

[PHV]

Furthermore, where is the evidence that the OP talked about a table ? ;-)