Clear a dynamic Range except first line - VBA

Posted on 2012-08-26
Last Modified: 2012-08-26
Hello All,

I have a dynamic range. I need a VBA to do the following:

ClearContent everything in that range but not do the clear content for the very first row of the dynamic range

a1      0
a2      0
a3      0
a4      0
a5      0
a6      0
a7      0
a8      0

after clear content > that clears everything except the first row of the dynamic range
a1      0

How do I do it via VBA?
Thank you
Question by:Rayne
    LVL 18

    Accepted Solution



    With Range("MyRange") '<< adjust the range name
        .Cells(1).Offset(1).Resize(.Rows.Count - 1, .Columns.Count).ClearContents
    End With

    Open in new window


    Author Comment

    Thank you Kris,
    This works perfect

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now