"Too many line continuations" when recording a marco

[sumncguy]

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

 

[smozgur]

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

If you have something like this:

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

then change it to look like following:

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]

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

If you have something like this:

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

then change it to look like following:

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]

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?

 

[Michael M]

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]

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]

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]

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]

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]

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.