How to delete entire.column between a Range.

[Trev0j]

Hello,

what I want to do is:

i have a ranged name" Adjusted" and ranged name " Start" which is located in A1. i want to delete entire columns between range "Start" and "Adjusted". wherever the range " Adjusted" is located.
btw, before this code, need to find the range "Adjusted" and delete all the columns in between A1

I am thankfull for every help!

 

[Trev0j]

Are both named ranges on the active sheet, when you ran the code?

Yes Sir. both is on the active sheet. A1 is always the "Start" and "Adjusted" will always be B1 after deleting the columns.

 

[Joe4]

Based on your responses, I am guessing Rick hit the nail on the head here:

Is "Start" and "Adjusted" actual Excel Defined Names or are they just range variables set within your code?

Maybe explain to us exactly how/where you created these "named" ranges.
If in VBA code, please post your code.

 

[Trev0j]

Based on your responses, I am guessing Rick hit the nail on the head here:

Maybe explain to us exactly how/where you created these "named" ranges.
If in VBA code, please post your code.

Sorry if i named it wrong,also sorry for my english but these are the headings of the columns.

i have just recorded a macro selecting the entire rows

Rows("1:1").Select
Selection.Find(What:="Adjusted", After:=ActiveCell, LookIn:=xlFormulas, _        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate...

and to delete the columns without specific count in between "Start" and "Adjusted"

 

[Joe4]

Yes, that makes a huge difference. A cell populated with that value and a named range are two entirely different things.
I think this will do what you want:

Sub MyDelete()

    Dim startCol As Long
    Dim adjCol As Long
    
'   Find Start column
    startCol = Rows("1:1").Find(What:="Start", After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
        
'   Find Adjusted column
    adjCol = Rows("1:1").Find(What:="Adjusted", After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column

'   Delete columns in between
    If adjCol - startCol > 1 Then
        Range(Cells(1, startCol + 1), Cells(1, adjCol - 1)).EntireColumn.Delete
    End If
    
End Sub

 

[Trev0j]

Yes, that makes a huge difference. A cell populated with that value and a named range are two entirely different things.
I think this will do what you want:

Sub MyDelete()

    Dim startCol As Long
    Dim adjCol As Long
    
'   Find Start column
    startCol = Rows("1:1").Find(What:="Start", After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
        
'   Find Adjusted column
    adjCol = Rows("1:1").Find(What:="Adjusted", After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column

'   Delete columns in between
    If adjCol - startCol > 1 Then
        Range(Cells(1, startCol + 1), Cells(1, adjCol - 1)).EntireColumn.Delete
    End If
    
End Sub

Thank you..it is working..
Thanks everyone!!!

 

[Joe4]

You are welcome.
Give Rick enough time, and he will probably come up with code that is much more concise than mine!

 

[Rick Rothstein]

Give Rick enough time, and he will probably come up with code that is much more concise than mine!

Not sure this would be considered more concise (is has more lines of code), but here is another macro that you can try...

Sub DeleteBetweenStartAndAdjusted()
  Dim A As Long, S As Long, C As Long, Row1 As Variant
  Row1 = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
  For C = 1 To UBound(Row1, 2)
    If Row1(1, C) = "Start" Then S = C + 1
    If Row1(1, C) = "Adjusted" Then A = C - 1
  Next
  Range(Columns(S), Columns(A)).Delete
End Sub