Cleaning up the UsedRange

[bradyboyy88]

I have a workbook and the first row is used for markers of important columns. The identifier basically lets me know there is important data in that column. I have been trying to shortening my looping of cells through used range but I am weak on it. First I want to loop through the first row of the usedrange which I assume is row 1 and then build my range of important columns. Then I want to loop through each of the cells of this range. to collect and do other modifications too. So how do I efficiently loop through in this method. Here is my code so far:

Sub copyTracker()


    Dim xlApp As Application
    Dim wb As Workbook
    Dim filepath As String
    Dim cell As Range
 
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    
    filepath = "C:\path\import.xlsx"
    
    xlApp.AskToUpdateLinks = False
    xlApp.DisplayAlerts = False
    Set wb = xlApp.Workbooks.Open(Filename:=filepath, ReadOnly:=True)
    xlApp.DisplayAlerts = True
    xlApp.AskToUpdateLinks = True
    
    For Each cell In wb.Sheets("Sheet1").UsedRange.Rows(1)
            'not sure how but I to take the intersect of the the UsedRange and these columns with markers and assign them to another range I can foreach through.
    Next cell
    
End Sub

 

[bradyboyy88]

the value name is wrong - it is xlCalculationManual
However - xlapp is not explicitly declared as Excel Application.
when work with external apps I would not use the native enumerated names but rather the actual values - this helps avoiding compilation errors.
In this particular case : xlCalculationManual=-4135
so it would look like:

[COLOR=#333333]xlApp.Calculation=-4135[/COLOR]

YOu were absolutely right! I have one more tweak since we are going through over 30000 cells in this new range. Some of these cells are blank so is there a way to remove all blanks from the range object similar to how rng.SpecialCells(xlCellTypeBlanks).Delete works but without shifting the cells around?

 

[bobsan42]

if I understand you correctly - to remove the blank cells from the definition of the range object, try something like this:

set rng = xlapp.union(rng.SpecialCells(xlCellTypeConstants),rng.SpecialCells(xlCellTypeFormulas))

untested but should work
if you have only values or only formulas in the non-blank cells you don't need union 'cos you will only need one type of special cells.