"Too many line continuations" when recording a marco

sumncguy

New Member
Joined
Jun 23, 2015
Messages
8
I have a large sheet that I need to delete / filter.
While recording a macro to filter a specific column, I receive too man line continuations.


I dont see where I can attach an example and the sheet is almost 8400 lines long.
I isolate the sheet, try to manually filter and rather than the filter being enabled on the header line a1, it is enabled on the second line a2.

Im guessing I have hidden chars in there some how ... Hope this explanation is good enough.

Any ideas ?

Thanks
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,287
Re: "Too many line continuations" when recordig a marco

If you have something like this:

Code:
myvar = "1abcd" & _
    "2abcd" & _
    "3abcd" & _
    "4abcd" & _
    '....many others.... like more than 25 lines...
    "8400abcd"

then change it to look like following:

Code:
myvar = "1abcd" & _
    "2abcd" & _
    "3abcd" & _
    "..20abcd"
myvar = myvar & "21abcd" & _
    "22abcd" & _
    "23abcd" & _
    "24abcd"
'...
'...

Basically, there is a limit that you can use & _ to create continuous code lines. Break it up to smaller pieces. It should help.

More info:
https://docs.microsoft.com/en-us/of...er-interface-help/too-many-line-continuations
 

sumncguy

New Member
Joined
Jun 23, 2015
Messages
8
Re: "Too many line continuations" when recordig a marco

If you have something like this:

Code:
myvar = "1abcd" & _
    "2abcd" & _
    "3abcd" & _
    "4abcd" & _
    '....many others.... like more than 25 lines...
    "8400abcd"

then change it to look like following:

Code:
myvar = "1abcd" & _
    "2abcd" & _
    "3abcd" & _
    "..20abcd"
myvar = myvar & "21abcd" & _
    "22abcd" & _
    "23abcd" & _
    "24abcd"
'...
'...

Basically, there is a limit that you can use & _ to create continuous code lines. Break it up to smaller pieces. It should help.

More info:
https://docs.microsoft.com/en-us/of...er-interface-help/too-many-line-continuations

Actually Im not writing the VB code. I am using the DATA > Macros > Record Macro button/ribbon/toolbar. The macro is never written because of the error so I cant eve see of the line characters are the issue.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Re: "Too many line continuations" when recordig a marco

Why can't you record it in 2 blocks and call the 2nd macro, then you have the complete code and then you can amend it as suggested?

Is the cell in the header row blank?
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Re: "Too many line continuations" when recordig a marco

Maybe you could post the code...or is it the code that is 8400 lines long ???
 

sumncguy

New Member
Joined
Jun 23, 2015
Messages
8
Re: "Too many line continuations" when recordig a marco

I am not writing this code in the VB code window. I am using the the ribbon to record the macro.

As a test, I tried to record the macro to filter only the column that I am receiving the error on.

There are 8k rows
There are many columns (didn't count them)
The macro records fine for deleting the unnecessary columns as I delete from left moving right.
At one point, I need to filter a particular column.
This is where the macro fails and the error is presented.

To troubleshoot
I return the sheet to the original state.
I retry the filter by manually pressing the filter down arrow and unchecking what I dont want to see in the column in question. No problem. These steps work as expected.

I then attempt to record a macro only on the column that I want filtered following the exact process executed during my manual test by using the ribbon / buttons. This is when I receive the line continuation error. The macro terminates execution and is never written. So there is no way I can forward the actual VB code because there isn't any ....
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,133
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Re: "Too many line continuations" when recordig a marco

I'd suspect the filter criteria are the issue as the recorder will list every single selected option in the filter. What are the criteria you are trying to filter by, and which is the smallest set - the ones to include, or to exclude?
 

sumncguy

New Member
Joined
Jun 23, 2015
Messages
8
Re: "Too many line continuations" when recordig a marco

I'd suspect the filter criteria are the issue as the recorder will list every single selected option in the filter. What are the criteria you are trying to filter by, and which is the smallest set - the ones to include, or to exclude?

This particular column lists network device model types, servers, routers, switches, wlc, AP, Load balancers, Firewalls etc etc. In this particular case, I want to filter only cisco devices.

The smaller set is / are the non cisco devices.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,133
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Re: "Too many line continuations" when recordig a marco

Can you use a custom filter for text that contains Cisco? That should remove the issue.
 

sumncguy

New Member
Joined
Jun 23, 2015
Messages
8
Re: "Too many line continuations" when recordig a marco

Can you use a custom filter for text that contains Cisco? That should remove the issue.

I can look at that ..havent tried got to see if I can do something like cisco | Nexus | WS kind of thing.

I was given this to work out for an admin. So it has to be setup so I can hand it back to her and say "press this, press that" and all she know is magic is happening.

If it were for me Id dump the entire sheet to the Linux command line via csv and write a bash script against it. Done. but not so fortunate.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,133
Messages
5,599,912
Members
414,346
Latest member
mmoose

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
Top