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
 
Sort by date Sort by votes
radeon187 said:
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.
Click to expand...
What do you mean?
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
@Anthony47 I'm thinking he is wanting to convert data in certain columns from megabytes to gigabytes?

@radeon187 Previously, i focused on suggesting the Select Case approach and overlooked the fact that your code snippet included a lot of selecting. You are probably aware that this is generally unnecessary and inefficient. Hence some of the other guys suggested loops that removed the selections. I would suggest that unless you have a real need to select, you do employ such looping.

You can still utilise the Select Case and I imagine that if used similar to the below, it will give you the means to selectively do the MB to GB conversion on the appropriate columns.
I'm not sure if the conversion is exactly as you want but hopefully, the principle is correct.

VBA Code: 
Sub radeon()

Dim MyRange As Range
Dim i As Integer

Worksheets("vInfo").Activate
Application.ScreenUpdating = False
For i = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1

   Select Case Cells(1, i).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", _
             "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"
             '^^^  Do Nothing
          
          Case "Provisioned (GB)", "Unshared (GB)", "Used (GB)"
            LastRow = Cells(Rows.Count, i).End(xlUp).Row
            Set MyRange = Range(Cells(2, i), Cells(LastRow, i))
            MyRange.Value = Evaluate(MyRange.Address & "/1000")
              '^^^ Convert MB to GB
            
          Case Else
            Columns(i).Delete
             '^^^ Delete column
           
    End Select
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
rlv01 said:
Yet another way, using the InStr function.
VBA Code: 
    .......
    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
    .......
Click to expand...
If you add a comma to the beginning and end of your ConditionStr, you can then use InStr to search for the cell value with commas on each side of it... that will insure a unique match. For the above snippet, you could do this instead...
VBA Code: 
    .......
    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),"

    If InStr(ConditionStr, "," & Trim(ActiveCell.Value) & ",") > 0 Then
    .......
 
Upvote 0
Rick Rothstein said:
If you add a comma to the beginning and end of your ConditionStr, you can then use InStr to search for the cell value with commas on each side of it... that will insure a unique match. For the above snippet, you could do this instead...
Click to expand...
Yeah, the "$" "%" bracketing chars were overkill, but I was in a hurry :). Your way is more efficient.
 
Upvote 0
Snakehips said:
@Anthony47 I'm thinking he is wanting to convert data in certain columns from megabytes to gigabytes?

@radeon187 Previously, i focused on suggesting the Select Case approach and overlooked the fact that your code snippet included a lot of selecting. You are probably aware that this is generally unnecessary and inefficient. Hence some of the other guys suggested loops that removed the selections. I would suggest that unless you have a real need to select, you do employ such looping.

You can still utilise the Select Case and I imagine that if used similar to the below, it will give you the means to selectively do the MB to GB conversion on the appropriate columns.
I'm not sure if the conversion is exactly as you want but hopefully, the principle is correct.

VBA Code: 
Sub radeon()

Dim MyRange As Range
Dim i As Integer

Worksheets("vInfo").Activate
Application.ScreenUpdating = False
For i = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1

   Select Case Cells(1, i).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", _
             "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"
             '^^^  Do Nothing
         
          Case "Provisioned (GB)", "Unshared (GB)", "Used (GB)"
            LastRow = Cells(Rows.Count, i).End(xlUp).Row
            Set MyRange = Range(Cells(2, i), Cells(LastRow, i))
            MyRange.Value = Evaluate(MyRange.Address & "/1000")
              '^^^ Convert MB to GB
           
          Case Else
            Columns(i).Delete
             '^^^ Delete column
          
    End Select
Next
Application.ScreenUpdating = True
End Sub
Click to expand...
This works like a charm! Thanks again. I'm about to post another thread that I think will me more complicated. Stay tuned.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,858
Messages
6,121,956
Members
449,057
Latest member
FreeCricketId

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