MS Excel / VBA help thread

I also (use to) have a macro that would go through conditional formatting in a s/s to reduce the number of times that the formatted ranges are duplicated & fragmented - that can impact size & performance, particularly if you have loads of (most often entirely unnecessary) complex formulae in the conditions.

Fragile af, though.

This is the best thing for cleaning up all the excess cell formatting. Only on certain versions of Office though which is annoying
https://support.microsoft.com/en-us/office/clean-excess-cell-formatting-on-a-worksheet-e744c248-6925-4e77-9d49-4874f7474738

My issue was the file was so unstable it crashed every time I tried to run VBA or recalculating, landed up just deleting worksheets until the file size shrunk identifying the offending location and then could just clear all the formatting and start again

You know you're deep in the woods when you're editing raw Excel xml files to clean up erroneous blank spaces.

I use it because I am personally blind as a bat/not intelligent and in tonnes of data find it easier to keep track

Can someone talk to me about Dax:
Calculate with all or all selected allows dynamic calculations based on filters etc.

When you're displaying such things on a page, how? As cards? What if someone probably wants a table of a number of these values as columns? Do I cry?

R users - I do Python(ish).

Is R easy enough to pick up?

Yes and no.

You'll be fine.

Anyone any good with Google sheets? I'm trying to pull the nth non blank cell from another sheet - formula works within same sheet but when I try and import range from the one I want to pull from I am getting the 'Argument must be a range' error.

What works within my sheet:

=ArrayFormula(INDEX($O$23:$O$300,SMALL(ROW($O$23:$O$300)+(100*($O$23:$O$300="")-22), 2))&")

What doesn't work when I try and applyh the above to data from another sheet:

=ArrayFormula(INDEX(IMPORTRANGE("Other sheet","Dashboard!Q4:Q50"),SMALL(ROW(IMPORTRANGE("Other sheet","Dashboard!Q4:Q50")+(100*(IMPORTRANGE("Other sheet","Dashboard!Q4:Q50")="")-3)),1))&"")

Any help greatly appreciated!

You have my sympathy.. Google sheets are awful. Pointless when excel exists IMO.

I have a load of workbooks that I want to split out each worksheet into multiple files with each new workbook being one tab from the original workbook (with the filename the same as the tab).

There are loads of example macros for this online and they are all basically identical. However, they don't work for me. All I get is one new blank workbook called sheet1 (no tabs are named sheet1 and there are no hidden tabs).

Sub SplitWorkbook()
    Dim workbookPath As String
    workbookPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each wSheet In ThisWorkbook.Sheets
        wSheet.Copy
        Application.ActiveWorkbook.SaveAs Filename:=workbookPath & "\" & wSheet.Name & ".xlsx"
        Application.ActiveWorkbook.Close False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

It falls down at wSheet.copy. Any bright ideas what is going wrong, it looks like it should work.

It does work for me, although I am using Excel 2010 👴

Next thing I would do is turn off the two alert/screen update suppressing lines, and work out why your loop seems to only be running once. How many members in ThisWorkbook.Sheets?

Code works for me in Excel for Microsoft 365

Application.ActiveWorkbook.Close False

Does this need an equals in it?

Edit: no it doesn't

Are you running the macro in a separate file, or in the file you want to split?

While struggling to even remember how to open the VBA IDE (Alt-F11 does something weird with graphics cards on my PC) I've just realised it is 20 years this month since I was employed as a full-time VBA developer. I should have asked for more...

I was running it in my personal workbook. Running it in the workbook that I want to split does work (but obviously isn't that practical). I'm guessing I may need to be more specific on which workbook/sheet it should be looking at.

@chez_jay Turning off the alerts doesn't give any errors. I suspect it's down to the file location.

Then your problem is 'This' here.

For Each wSheet In ThisWorkbook.Sheets

-

I'm guessing I may need to be more specific on which workbook/sheet it should be looking at.

I would suggest keeping the code in a utility workbook, and Calling it from the workbook you want to split, in order to avoid this

I think you can save it in your personal workbook, but to run you open both, and then switch to the workbook you want to split, and run the macro from the personal workbook.

ThisWorkbook

refers to the one with the focus when you run the macro.

Cheers all. Changing ThisWorkbook to ActiveWorkbook solved it

Ah cool. For my future reference:

'ActiveWorkbook' is the workbook that is currently active (similar to how ActiveSheet is the currently active sheet). 'ThisWorkbook' is the workbook where the VBA code is stored.

I know this isn't the right thread but I don't think we have a generic "I work in Analytics" thread and this probably has the highest crossover.

If anybody works in Marketing Analytics, I'm kind of stuck a bit on some work. iOS15 brought in some changes which means Open Rates are affected and the prediction is that Open Rates would go up because "if one person on iOS15 opens the email then everybody who received the email on an iOS15 device will be registered as opening the email". This is what I've read online from multiple sources, I don't really have anyone more experienced or superior to bounce this off.

Part of the problem in confirming this is that when you send an email, you don't know which device or OS it's been sent to, only when they open it. So you can't isolate iOS15 sends and then see which % opened. The only way I know to really examine this is to layer OS ratio over Open Rate, so see how the breakdown of all the OSs has changed since the September release date.

Following the above theory, I should be seeing a big spike in the ratio of opens from iOS15 users, but I'm not. I'm seeing a small increase from when it was released, but I'm actually seeing a big increase in non-iOS and non-Android opens which suggests to me that actually iOS15 is obfuscating the data it sends back to the sender's email tool.

The client basically thinks we're (because obviously we're the only agency saying this??) bullshitting and the meteoric rise in Open Rates is because actually their email engagement improvements are working, I was hoping to make things clearer, but I really can't because I can't make sense of what I'm seeing.

Has anyone got any experience in analysing wtf is going on?

I do work in marketing analytics, but sadly not in a useful area for this question. You may get some traction in

https://www.lfgss.com/conversations/137459/?offset=525#comment16298193

or

https://www.lfgss.com/conversations/298883/#comment16228528

I know there are plenty of people who work in media\marketing who are active in those, and might be more useful than me.

Why not do a test? Send out to 10 people with devices where you know the OS and check what happens as people open it.

I had proposed that idea with a different client but they were happy to take the word of Litmus who are pretty much the authority on Email testing and evaluation.

With this client I think I'll have to propose an internal test using colleagues and internal iOS15 devices so I can see if OR is 100% without anybody opening the email.

Cheers!