Microsoft Office Q&A: General Discussion (Word, Excel, PowerPoint...)

Nicholas King said:Sub Macro1()

BrojListova = Application.Sheets.Count

For i = 1 To BrojListova
Sheets(i).Select

....

Next i

End Sub

So, that’s how you set up a loop to run a macro across all sheets.
What I’m looking for is how to make the macro start from whichever sheet is currently active and then proceed forward from there. For example:
Suppose we have: sheet1, sheet2, sheet3, sheet4
If I'm working on sheet3 and trigger the macro, I want it to run for sheet3, sheet4 (and sheet5 if it exists), but absolutely skip sheet1 and sheet2.

Larry Peterson30 said:So, I'm currently working on sheet3 and I call a macro; that macro is supposed to run on sheet3, sheet4 (and sheet5 if it actually exists), but definitely not on sheet1 or sheet2.

Replace the loop with

For i = ActiveSheet.Index To Brojlistov Sheets

See, this logic relies on the actual order of the sheets within the workbook (the way they appear in the tabs at the bottom), rather than their names—so if you were to drag sheet3 over to the very first position, the loop would simply start from 1.

Hi there,

I have a bit of a question... I was wondering if anyone might know a specific formula for data Validation that could, say, prevent someone from entering anything into cell A2 unless cell A1 actually has something typed into it?
Basically, I’m trying to ensure that within a single row, entries have to follow a strict sequence—A1, then A2, then A3, and so on...
It’s so you can't type anything into cells A2, A3, or A4 if A1 is still sitting there empty.
And then, if A1 is filled but A2 is still blank, you wouldn't be able to enter anything into A3 or A4 because A2 hasn't been completed yet.

I hope I haven't made things overly complicated with my explanation? 🙂

Thanks,

cosmicbison19 said:Does anyone happen to know a Data Validation formula that would, say, prevent someone from entering anything into cell A2 unless A1 actually has something typed into it?

=COUNTBLANK($A$1)=0

For the subsequent cells—let's say A3—you could theoretically set it up like this:

=COUNTBLANK($A$1:$A$2)=0

though, honestly, you probably don't even need to—it's logically impossible for A1 to be empty while A2 isn't, right?

Now, if I were following my own internal logic,

=NOT(ISBLANK($A$1))

should technically work... but for some reason, I can't for the life of me figure out why it doesn't. 🙂

Nicholas King said:=COUNTBLANK($A$1)=0

For any other cells, you could probably use something like this for A3:

=COUNTBLANK($A$1:$A$2)=0

though, honestly, you don't really need to—it's pretty much impossible for A1 to be empty while A2 isn't.

If I were following my own internal logic, I suppose

=NOT(ISBLANK($A$1))

should technically work, but for some reason, it just doesn't seem to behave. 🙂

Okay, so that works, but it only blocks entries if A1 specifically is empty.
What I'm actually looking for is a way to prevent someone from entering data if the cell immediately preceding it is blank. For instance, if A1 through A4 are all filled out, I want to make sure they can't skip ahead and type something into A6.
Basically, I want to force them to enter data in a specific sequence.

thanks

cosmicbison19 said:you gotta enter stuff into the cells in a specific order.

Say you're working with A1:A10
A1: no Data Validation applied
A2: =COUNTBLANK($A$1)=0
grab the range then hit Data Validation => Custom
A3:A10 =COUNTBLANK($A$1:$A2)=0

cosmicbison19 said:Alright, this works—technically—but it’s only going to block input in other cells if cell A1 happens to be empty.

Well, I suppose it goes without saying that you’re going to have to adjust the formula for whatever specific cell you’re applying it to. 🙂--it isn't exactly a "set it and forget it" situation, after all.--

For A3, you'd use =COUNTBLANK($A$2)=0, and so on.

Nicholas King said:Duh, you obviously have to tweak the formula for whichever cell you're dropping it into.
For A3, just use =COUNTBLANK($A$2)=0, and keep going from there.

You got a point there, @Nicholas King/">@@Nicholas King, but I was just trying to save him the headache of doing it cell by cell instead of letting him just grab the whole range.🙂
Later.

Kyle Johnson2 said:You make a fair point, Nicholas King—though, I suppose, my intention was just to save him some unnecessary grief by not forcing him to go cell by cell; I figured selecting the whole range 🙂
would be more efficient.
Best,

Actually, your suggestion is probably better—it’s quicker to type, I guess.

By the way, does anyone know why =NOT(ISBLANK... isn't working for me? 😕

That actually works, surprisingly enough 🙂
thanks

Kyle Johnson2 said:Suppose you're working with a range like A1:A10
A1: leave it without any Data Validation
A2: =COUNTBLANK($A$1)=0
just select the range then go to Data Validation => Custom
A3:A10 =COUNTBLANK($A$1:$A2)=0

Hey there...

So, I’m sitting here at work staring at this massive Excel spreadsheet with about 18,000 rows of data, and I’ve run into a bit of a situation. I need to scrub out all the duplicates—basically anything that shows up two or more times...

The layout is pretty straightforward: Column A has the company names, Column B has their IDs, Column C is the address, and so on...

Does anyone happen to know the easiest way to handle this without me having to manually scroll through all 18,000 entries? I mean, if I tried to do this by hand, I guess I'd be stuck at my desk for an entire week...😢((

Joseph Thompson10 said:Hey there...
Can anyone please tell me how to pull this off without having to manually scroll through all 18,000 rows? It’ll take me an entire week if I try to do it by hand. 😢((

Try this instead: Go to the Data tab, hit Filter, then select Advanced Filter. In the List range box, just highlight the data you're working with, check the box for Unique Records only, and hit OK.

Joseph Thompson10 said:Got handed this massive Excel sheet at work with like 18,000 rows and I need to scrub out anything that shows up two or more times...

Adding to what Walter Jackson48 suggested, try running this formula too:
=IF(COUNTIF($A$2:A2;A2)>1;"DUPLICATE";"OK")
If you’re dealing with duplicate data spread across multiple columns, check out this fix HERE

If you're using Excel 2007, just hit up the Data tab and click remove duplicates.

Thanks so much!!! It’s all sorted out now! 🙂

Could someone give me a hand with Access 2003?

I'm trying to run some analysis on two different tables I have sitting in Excel—one covers the period ending April 30th and the other ends May 31st. Since both files are pretty massive with endless rows and columns, I figured using Access might actually save me some time instead of staring at spreadsheets all day.
I already know which field should serve as my primary key in those XLS files, but does anyone know how to set up a query to pull out just these specific things:
1. For instance, a list of clients where their balance actually changed,
2. A list of clients who showed up for the first time in the April 30th table,
3. And a list of clients making their debut in the May 31st table?

Is there a way to wipe every single image out of a Microsoft Word document in one fell swoop? How do I pull that off? 😁

Elizabeth Fowler46 said:Is there any way to just wipe out every single image in a Microsoft Word document all at once? How do I do it? 😁

Well, if you want to play it by ear, you could try holding down the Ctrl key while you click through them to select everything, then just hit Delete. It’s a bit tedious, honestly—I remember spending an hour doing something similar back when I was working on a massive report for my old firm in Chicago—but if you hold Ctrl while clicking each one, you can eventually just delete the whole lot. That’s it, assuming we're on the same page.

Walter Jackson48 said:If you just hold down the Ctrl key while you click through them to select everything, why not? Just grab the first one, hold Ctrl while you pick out the rest, and then hit Delete. It’s simple enough if you follow my drift.

I get how that works, but I was honestly hoping there was some kind of "Delete all images" command buried somewhere in the menu. 😁
Clicking through every single one manually feels like such a massive waste of time.

Code: