Please guide how to completely delete UsedRange in Excel 2007? RRS feed

  • Question

  • I'm working on a data analysis MACRO via Excel 2007.   Since I want to get the columns count of one sheet,  I choose  Activsheet.UsedRange.Column.Count to get it.  Please find the script as following lines:


       intColumnCount_ContinuousService_Vertical = xlsSheetContinuousServiceVertical.UsedRange.Columns.Count
       strLastColumnName_ContinuousService_Vertical = Split(xlsSheetContinuousServiceVertical.Cells(1, intColumnCount_ContinuousService_Vertical).Address, "$")(1)
       xlsSheetContinuousServiceVertical.Columns("A:" & strLastColumnName_ContinuousService_Vertical).Delete (xlShiftToLeft)

       intColumnCount_ContinuousService_Vertical = xlsSheetContinuousServiceVertical.UsedRange.Columns.Count

       MsgBox intColumnCount_ContinuousService_Vertical


    Everytime I reopen the excel file, It runs well, However, If I run the Marco second time , I noticed intColumnCount_ContinuousService_Vertical > 1. 

    Please guide how can I completly delete "UsedRange" Area in the sheet?

    Tuesday, June 6, 2017 1:41 AM

All replies

  • UsedRange will be always there. If you insert a new sheet, it will have usedrange "A1"

    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Tuesday, June 6, 2017 9:07 AM
  • Hi,

    I notice that the issue is related to the Macro using in Excel 2007. This forum focuses on Excel general discussion about Excel build-in features.

    To better resolve your issue, I would move the thread to Excel for developers forum for more help. Thanks for your understanding.


    Winnie Liang

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact

    Wednesday, June 7, 2017 8:45 AM