Too many line continuations, need consolidation with OR statement.

R

radeon187

New Member
Joined
Mar 25, 2020
Messages
28
Office Version
  1. 365
Platform
  1. Windows
I know there's a limit of 24 line continuations, but is there any way to fix the following? Im basically using this code to go through each sheet and only keep the column headers listed within the list. So if I break it apart into two Subs, everything in the 2nd sub will be removed when the 1st sub is ran. I need to have a way to keep all of the specified columns within a single sub. I read something that said you could use the "+" to consolidate the lines but I haven't been able to figure that out.

This is the current code that is obliviously too long:
VBA Code: 
Sub newcolumnremove()
Worksheets("vInfo").Activate
Range("A1").Select

Do Until ActiveCell.Value = ""

    If ActiveCell.Value = "vCenter Server" _
        Or ActiveCell.Value = "vCenter Version" _
        Or ActiveCell.Value = "VM" _
        Or ActiveCell.Value = "Powerstate" _
        Or ActiveCell.Value = "DNS Name" _
        Or ActiveCell.Value = "CPUs" _
        Or ActiveCell.Value = "Memory" _
        Or ActiveCell.Value = "NICs" _
        Or ActiveCell.Value = "Latency Sensitivity" _
        Or ActiveCell.Value = "Primary IP Address" _
        Or ActiveCell.Value = "Network #1" _
                Or ActiveCell.Value = "Network #2" _
                Or ActiveCell.Value = "Network #3" _
                Or ActiveCell.Value = "Network #4" _
                Or ActiveCell.Value = "Num Monitors" _
                Or ActiveCell.Value = "Video Ram KB" _
                Or ActiveCell.Value = "Resource pool" _
                Or ActiveCell.Value = "Folder" _
                Or ActiveCell.Value = "vApp" _
                Or ActiveCell.Value = "FT State" _
                Or ActiveCell.Value = "Provisioned (GB)" _
                Or ActiveCell.Value = "Used (GB)" _
                Or ActiveCell.Value = "Unshared (GB)" _
                Or ActiveCell.Value = "HA Restart Priority" _
                Or ActiveCell.Value = "HA VM Monitoring" _
                Or ActiveCell.Value = "Cluster rule(s)" _
                Or ActiveCell.Value = "Cluster rule name(s)" _
                Or ActiveCell.Value = "Firmware" _
                Or ActiveCell.Value = "HW version" _
                Or ActiveCell.Value = "Path" _
                Or ActiveCell.Value = "Log directory" _
                Or ActiveCell.Value = "Snapshot directory" _
                Or ActiveCell.Value = "Suspend directory" _
                Or ActiveCell.Value = "Annotation" _
                Or ActiveCell.Value = "Description" _
                Or ActiveCell.Value = "Environment" _
                Or ActiveCell.Value = "Datacenter" _
                Or ActiveCell.Value = "Cluster" _
                Or ActiveCell.Value = "Host" _
                Or ActiveCell.Value = "VM ID"_
                Or ActiveCell.Value = "HA Isolation Response" Then
ActiveCell.Offset(0, 1).Select

    Else

        ActiveCell.EntireColumn.Select
        Selection.Delete Shift:=xlToLeft
        Selection.End(xlUp).Select

    End If
Loop


I did the following, but this doesn't work like i need it to.
VBA Code: 
Sub one()
Worksheets("vInfo").Activate
Range("A1").Select

Do Until ActiveCell.Value = ""

    If ActiveCell.Value = "vCenter Server" _
        Or ActiveCell.Value = "vCenter Version" _
        Or ActiveCell.Value = "VM" _
        Or ActiveCell.Value = "Powerstate" _
        Or ActiveCell.Value = "DNS Name" _
        Or ActiveCell.Value = "CPUs" _
        Or ActiveCell.Value = "Memory" _
        Or ActiveCell.Value = "NICs" _
        Or ActiveCell.Value = "Latency Sensitivity" _
        Or ActiveCell.Value = "Primary IP Address" _
        Or ActiveCell.Value = "Network #1" _
                Or ActiveCell.Value = "Network #2" _
                Or ActiveCell.Value = "Network #3" _
                Or ActiveCell.Value = "Network #4" _
                Or ActiveCell.Value = "Num Monitors" _
                Or ActiveCell.Value = "Video Ram KB" _
                Or ActiveCell.Value = "Resource pool" _
                Or ActiveCell.Value = "Folder" _
                Or ActiveCell.Value = "vApp" _
                Or ActiveCell.Value = "FT State" _
                Or ActiveCell.Value = "Provisioned (GB)" _
                Or ActiveCell.Value = "Used (GB)" _
             Or ActiveCell.Value = "Unshared (GB)" Then
ActiveCell.Offset(0, 1).Select

    Else

        ActiveCell.EntireColumn.Select
        Selection.Delete Shift:=xlToLeft
        Selection.End(xlUp).Select

    End If
Loop
End Sub
Sub two()
Worksheets("vInfo").Activate
Range("A1").Select

Do Until ActiveCell.Value = ""

 If ActiveCell.Value =  "HA Restart Priority" _
                Or ActiveCell.Value = "HA VM Monitoring" _
                Or ActiveCell.Value = "Cluster rule(s)" _
                Or ActiveCell.Value = "Cluster rule name(s)" _
                Or ActiveCell.Value = "Firmware" _
                Or ActiveCell.Value = "HW version" _
                Or ActiveCell.Value = "Path" _
                Or ActiveCell.Value = "Log directory" _
                Or ActiveCell.Value = "Snapshot directory" _
                Or ActiveCell.Value = "Suspend directory" _
                Or ActiveCell.Value = "Annotation" _
                Or ActiveCell.Value = "Description" _
                Or ActiveCell.Value = "Environment" _
                Or ActiveCell.Value = "Datacenter" _
                Or ActiveCell.Value = "Cluster" _
                Or ActiveCell.Value = "Host" _
                Or ActiveCell.Value = "VM ID" _
                Or ActiveCell.Value = "HA Isolation Response" Then
ActiveCell.Offset(0, 1).Select

    Else

        ActiveCell.EntireColumn.Select
        Selection.Delete Shift:=xlToLeft
        Selection.End(xlUp).Select

    End If
Loop
End Sub
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
You could use select case like this:
VBA Code: 
Sub Select_Case()
'Modified 3/29/2020 11:29:50 AM EST
Application.ScreenUpdating = False
Select Case ActiveCell.Value
Case "Alpha", "Bravo", "Charlie", "Delta"
ActiveCell.Offset(, 1).Value = "Hello"
End Select
Application.ScreenUpdating = True
End Sub
 
Upvote 0
@radeon187 I haven't included anywhere near all of your test strings but if you employ Select Case you should reduce the continuations significantly.

VBA Code: 
Do Until ActiveCell.Value = ""

    Select Case ActiveCell.Value
  Case "vCenter Server", "vCenter Version", "VM", "Powerstate", "DNS Name", "CPUs", _
         "Memory", "NICs" 'etc etc

        ActiveCell.Offset(0, 1).Select

   Case Else

        ActiveCell.EntireColumn.Select
        Selection.Delete Shift:=xlToLeft
        Selection.End(xlUp).Select
End Select
  
Loop
 
Upvote 0
You can do it like
VBA Code: 
Sub radeon()
   Dim Ary As Variant, Res As Variant
   Dim Cl As Range, Rng As Range
   
   Ary = Array("vCenter Server", "vCenter Version", "VM", "Powerstate", "DNS Name", "CPUs", "Memory", _
            "NICs", "Latency Sensitivity", "Primary IP Address", "Network #1", "Network #2", "Network #3", _
            "Network #4", "Num Monitors", "Video Ram KB", "Resource pool", "Folder", "vApp", "FT State")
            
   For Each Cl In Range("A1", Cells(1, Columns.Count).End(xlToLeft))
      Res = Application.Match(Cl.Value, Ary, 0)
      If IsError(Res) Then
         If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
      End If
   Next Cl
   If Not Rng Is Nothing Then Rng.EntireColumn.Delete
End Sub
Just add the rest of the values to the array
 
Upvote 0
You don't need a new line for each 'Or' although there are many cleaner ways to do it. I would have done a better re-write, but wasted too much time trying to get your code into the editor to begin with.
VBA Code: 
Sub newcolumnremove()
Worksheets("vInfo").Activate
Range("A1").Select

Do Until ActiveCell.Value = ""

    Select Case ActiveCell.Value
        Case "vCenter Server", "vCenter Version", "VM", "Powerstate", "DNS Name", "CPUs", "Memory", _
            "NICs", "Latency Sensitivity", "Primary IP Address", "Network #1", "Description", "Environment", _
            "Datacenter", "Cluster", "Host", "VM ID", "HA Isolation Response"
               
                ActiveCell.Offset(0, 1).Select
        Case Else
            ActiveCell.EntireColumn.Select
            Selection.Delete Shift:=xlToLeft
            Selection.End(xlUp).Select
   
    End Select
Loop
End Sub
edit: looks like @Fluff has more patience that the rest of us
 
Upvote 0
Another possible approach, in addition to what suggested by @My Aswer Is This
VBA Code: 
Valid = Array("vCenter Server", "vCenter Version", "VM", "and so on")   '<<< FILL YOUR LONG LIST

If IsError(Application.Match(ActiveCell.Value, Valid, False)) Then
    'What to do if ActiveCell.Value is not in the list
Else
    'What to do if ActiveCell.Value is in the list
End If

Bye

EDIT: "my keyboard" was slower than sombodyelse one...
 
Upvote 0
This uses Application.Match and an Array

VBA Code: 
Sub newcolumnremove()
    Dim MyList As Variant
  
    MyList = Array("vCenter Server", "vCenter Version", "VM", "Powerstate", "DNS Name", "CPUs", "Memory", _
            "NICs", "Latency Sensitivity", "Primary IP Address", "Network #1", "Description", "Environment", _
            "Datacenter", "Cluster", "Host", "VM ID", "HA Isolation Response")

  
    For i = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
      
        If IsError(Application.Match(Cells(1, i).Value, MyList, 0)) Then Columns(i).Delete
    
    Next i
  
End Sub
 
Upvote 0
Thanks everyone for the multiple ways of getting this done. I went with the following and it works great.

VBA Code: 
Worksheets("vInfo").Activate
Range("A1").Select

Do Until ActiveCell.Value = ""

   Select Case ActiveCell.Value
        Case "vCenter Server", "vCenter Version", "VM", "Powerstate", "DNS Name", "CPUs", "Memory", _
            "NICs", "Latency Sensitivity", "Primary IP Address", "Network #1", "Network #2", "Network #3", _
            "Network #4", "Num Monitors", "Video Ram KB", "Resource pool", "Folder", "vApp", "FT State", "Provisioned (GB)", _
            "Used (GB)", "Unshared (GB)", "HA Restart Priority", "HA VM Monitoring", "Cluster rule(s)", "Cluster rule name(s)", _
            "Firmware", "HW version", "Path", "Log directory", "Snapshot directory", "Suspend directory" , "Annotation", _ 
            "Description", "Environment", "Datacenter", "Cluster", "Host", "VM ID", "HA Isolation Response"
               
                ActiveCell.Offset(0, 1).Select
        Case Else
            ActiveCell.EntireColumn.Select
            Selection.Delete Shift:=xlToLeft
            Selection.End(xlUp).Select
   
    End Select
Loop


Now next thing I have to do is convert specific columns like "Provisioned (GB)" and "Used (GB)" from their original MB value within the column to the actual GB value.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
Yet another way, using the InStr function.
VBA Code: 
    Dim ConditionStr As String
    
    ' "Or" conditions
    ConditionStr = "vCenter Version,VM,Powerstate,DNS Name,CPUs,Memory,NICs,Latency Sensitivity,Primary IP Address," _
                 & "Network #1,Network #2,Network #3,Network #4,Num Monitors,Video Ram KB,Resource pool,Folder,vApp,FT State," _
                 & "Provisioned (GB),Used (GB),Unshared (GB)"

    ' Insure a unique match
    ConditionStr = "$" & VBA.Replace(ConditionStr, ",", "%,$", 1)

    If InStr(1, ConditionStr, "$" & Trim(ActiveCell.Value) & "%") > 0 Then
        ActiveCell.Offset(0, 1).Select
    Else

        ActiveCell.EntireColumn.Select
        Selection.Delete Shift:=xlToLeft
        Selection.End(xlUp).Select
    End If
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,522
Messages
6,120,020
Members
448,938
Latest member
Aaliya13

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top