Clearing data in Excel

[DKL01]

Hi,

I have navigation bar in my excel. Based on the selection the macro connects to database, retrieves and displays the data below the navigation bar ( from row # 10 ). User can change the criteria and submit for new data.

I was wondering how the clear the previous data (including content and formatting but without clearing the navigation bar in rows 1 thru 10 ) using macro prior to redisplaying data. I know the starting point (Row # 10) but end point is dynamic.

I really appreciate any suggestions.

Thanks

 

[SkipVought]

Hi,

Here's a way...

Sub ClearDataArea()
    Dim lLastRow As Long, iLastCol As Integer
        
    With ActiveSheet.UsedRange
        lLastRow = .Row + .Rows.Count - 1
        iLastCol = .Column + .Columns.Count - 1
    End With
    
    Range(Cells(10, 1), Cells(lLastRow, iLastCol)).ClearContents
End Sub

Hope this helps Skip,
metzgsk@voughtaircraft.com