Probably a simple Excel query!

[OCdude]

I'm currently bulk-renaming some chapter files by using a very basic CONCATENATE function. The process is:

1. I paste the source data into column A
2. CONCATENATE then combines Column A data with Column C data, then outputs it in Column E (Essentially all i want is for the alternating cells that contain "CHAPTERxxNAME=" to be appended with the corresponding Chapter name, e.g. "CHAPTER01NAME=" becomes "CHAPTER01NAME=Chapter 1"
3. Conditional formatting in Column E 'hides '(changes the font to white) any cell that starts "Chapter " (i.e. any data after the last line of pasted data)

Currently, I'm manually selecting, copying and pasting the end result from Column E, but i'd like to use a Macro to just copy it for me (trust me, when you're doing hundreds of these, every tiny time saving measure helps!)

Try as I might, I can't find a simple way of selecting all the visible data in Column E. It always selects the 'hidden' cells. (Because the episodes usually contain varying numbers of chapters, I can't just select a fixed range of cells).

I've tried selecting cells that are only using the black font colour, filtering, (IF=(ISBLANK)) and various other stuff, but whatever I do, it selects the whole damn lot

Appreciate your input. I'm sure I'm overlooking something simple :/

Thanks in advance!

To give you a visual: This is what happens if i click into Column E and do a ctrl-a

 

[h4rm0ny]

I can help with this, but let me just make sure I understand what you actually want because I'm confused. Would I be right in saying that you've managed to get the results you want in Column E, but that you only want to copy and paste those cells in Column E that begins with "Chapter" ? This last bit is the part I think is confusing me because ALL of the cells in your attached image in column E begin with "CHAPTER".

EDIT: Wait, I think I understand. In your image you would want to copy E2:E31 and it's rows E32 and downwards you want to avoid including because they do have contents but I simply can't see it in the screenshot because you've conditionally changed the font colour to white? Do I have that correct? If so, the first thing I would recommend is NOT using Conditional Formatting to hide information. That's just a display tool for humans. What you really want is probably for there to be no information if there's not supposed to be any. So change your formula to something like:

=IF(ISBLANK(A2),"",CONCATENATE(A1,C1))

At least now you're starting from a better place. When you copy E now, it will actually only have the information you want. I know you mentioned that you tried ISBLANK() but the way you have it written in your post is wrong, so I figured maybe this was it.

EDIT EDIT: There's a tiny little gotcha with ISBLANK() which could be tripping you up, though I suspect this isn't the problem. But I'll mention it just in case. ISBLANK() only works for truly blank cells. If the cell has a formula in it, that doesn't count as blank. So if the contents of Column A are actually the result of a formula that simply returns "" sometimes, but isn't actually empty, then ISBLANK() wont work for you. I don't think this is the reason it didn't work for you but if it is, then what you want to do is use COUNTBLANK() instead of ISBLANK(). You'd simply use it something like this:

=IF(COUNTBLANK(A2)>0,"",CONCATENATE(A1,C1))

I'm fairly sure that's not it so stick with the first one. But if it is it, that's the workaround. You could also just use

=IF(A2="","",CONCATENATE(A1,C1))

I prefer ISBLANK for reasons to do with more sophisticated things I might want to do later on, but it's not worth getting into that. The A2="" in your IF function would be perfectly fine for your purposes.

 

[OCdude]

I can help with this, but let me just make sure I understand what you actually want because I'm confused. Would I be right in saying that you've managed to get the results you want in Column E, but that you only want to copy and paste those cells in Column E that begins with "Chapter" ? This last bit is the part I think is confusing me because ALL of the cells in your attached image in column E begin with "CHAPTER". Negative!

EDIT: Wait, I think I understand. In your image you would want to copy E2:E31 and it's rows E32 and downwards you want to avoid including because they do have contents but I simply can't see it in the screenshot because you've conditionally changed the font colour to white? Do I have that correct? Correct! If so, the first thing I would recommend is NOT using Conditional Formatting to hide information. That's just a display tool for humans. What you really want is probably for there to be no information if there's not supposed to be any. So change your formula to something like:

=IF(ISBLANK(A2),"",CONCATENATE(A1,C1))

At least now you're starting from a better place. When you copy E now, it will actually only have the information you want. I know you mentioned that you tried ISBLANK() but the way you have it written in your post is wrong, so I figured maybe this was it.

EDIT EDIT: There's a tiny little gotcha with ISBLANK() which could be tripping you up, though I suspect this isn't the problem. But I'll mention it just in case. ISBLANK() only works for truly blank cells. If the cell has a formula in it, that doesn't count as blank. So if the contents of Column A are actually the result of a formula that simply returns "" sometimes, but isn't actually empty, then ISBLANK() wont work for you. I don't think this is the reason it didn't work for you but if it is, then what you want to do is use COUNTBLANK() instead of ISBLANK(). You'd simply use it something like this:

=IF(COUNTBLANK(A2)>0,"",CONCATENATE(A1,C1))

I'm fairly sure that's not it so stick with the first one. But if it is it, that's the workaround. You could also just use

=IF(A2="","",CONCATENATE(A1,C1))

I prefer ISBLANK for reasons to do with more sophisticated things I might want to do later on, but it's not worth getting into that. The A2="" in your IF function would be perfectly fine for your purposes.

Thanks for the detailed response

Unfortunately, using either of the formulas above, results in the same issue Because there's 'stuff' in those empty cells, they're still being included in the selection. Even if I try any of the options under Find and select > Go To Special.... it always pics the entire region.

I'm wondering if this is something that would have to be done using VBA?! (Most of my seemingly simple Excel queries end up that way lol).

 

[h4rm0ny]

Thanks for the detailed response

Unfortunately, using either of the formulas above, results in the same issue Because there's 'stuff' in those empty cells, they're still being included in the selection. Even if I try any of the options under Find and select > Go To Special.... it always pics the entire region.

I'm wondering if this is something that would have to be done using VBA?! (Most of my seemingly simple Excel queries end up that way lol).

Are you absolutely sure you're still getting results for those cells using the formula I gave you above? I could see that you might still get empty lines at the end of your selection, but not actual content. I mean if you go to E2 and hit Ctrl+Shift+Down Arrow it will highlight as far down as you have formula in those cells, that's true. But copy the contents and they'll be blank lines.

I can give you some VBA code that will select specifically the region that has content in it (ignoring Formula). But I feel like I'm missing something because what I suggested should work. You're seeing what you expect on screen right? And you've got rid of all the conditional formatting so there's nothing hidden that just think is empty?

 

[OCdude]

Ah yes, I should have mentioned, while I now don't get the previously hidden "Chapter " text pasted at the bottom, I do still get blank lines... whiiiiiiich ideally i'd like to not have, as it can cause issues.

So I reckon you've probably done it the best way, but just might need the faff of VBA to get exactly what I need

Thanks!

 

[h4rm0ny]

Ah yes, I should have mentioned, while I now don't get the previously hidden "Chapter " text pasted at the bottom, I do still get blank lines... whiiiiiiich ideally i'd like to not have, as it can cause issues.

So I reckon you've probably done it the best way, but just might need the faff of VBA to get exactly what I need

Thanks!

Okay. So I fixed what I thought your problem was, at least.

I'm semi-inclined to suggest your question might be wrong. I don't know what the before stages and after stages of this process are - I can only see the Excel bit in the middle. But my hunch is that maybe there are other ways to go about it. For example, rather than faff around trying to select cells without formulae in them, you could just accept the blank lines and strip them from the files later. I'm more of a Linux person than a Windows one, but in Powershell you would do something like this:

(gc fileToEdit.txt) | ? {$_.trim() -ne "" } | set-content fileToEdit.txt

And that would strip the blank lines from the file called "fileToEdit.txt". You could apply that to all files in a directory fairly easily.

But if you want a Macro that will select down a column until you start hitting blanks, here's a quick example. N.b. Excel is not really my field so there may be better ways someone else has to do this:

Sub SelectTillBlank()

    ''FIRST WE CREATE SOME VARIABLES WE NEED.
    
    Dim rowIndex As Integer ''This is where we keep the row we're looking at.
    Dim rowMax As Integer   ''This is how many rows we're going to keep searching for
    Dim colNum As Integer   ''This is the column we're going to search. A=1, B=2, etc.
    Dim exportRange As Range    ''This is where we're going to store our answer.
    
    ''NOW WE SET OUR DEFAULTS - COLUMN WE'RE SEARCHING, HOW LONG WE'LL SEARCH
    
    rowMax = 120            ''Lets assume less than sixty chapters (60x2)
    colNum = 5              ''This is Col. E
    
    ''NOW WE DO THE USEFUL STUFF
    
    For rowIndex = 2 To rowMax                      ''This starts our loop down the column
        If (Sheet1.Cells(rowIndex, colNum) = "") Then    ''This shows we've found a blank space
            Exit For                                ''So because we've started finding blanks, we stop and exit the loop here.
        End If
    Next
    
    Set exportRange = Sheet1.Range(Sheet1.Cells(2, colNum), Sheet1.Cells(rowIndex - 1, colNum)) ''Now we set the range to be from the start to however far we got with RowIndex
    exportRange.Select  ''And now we tell that range to select itself.
    
End Sub

So create a Macro and make the above the sole contents of it (you only want one Sub and one End Sub). When you run it, it should select from E2 down until you hit blanks. N.b. it assumes that you haven't changed the name of the sheet from "Sheet1".

 

[OCdude]

Legend, that works - thanks

One final thing - what do i need to add to finally just copy the now-selected data to the clipboard? (I know zero VB!)

Ideally, i'd like one single button that would:

1. Paste the clipboard into A2
2. Select the data in column E (as per your VB script above)
3. Copy that data to the clipboard
4. Clear the contents of A2:A81

At the moment i'm using 3 separate buttons for each (Paste / Select / Clear)

Hope i'm not being cheeky now
Thanks again!

 

[h4rm0ny]

Legend, that works - thanks

One final thing - what do i need to add to finally just copy the now-selected data to the clipboard? (I know zero VB!)

Ideally, i'd like one single button that would:

1. Paste the clipboard into A2
2. Select the data in column E (as per your VB script above)
3. Copy that data to the clipboard
4. Clear the contents of A2:A81

At the moment i'm using 3 separate buttons for each (Paste / Select / Clear)

Hope i'm not being cheeky now
Thanks again!

I have fed the baby bird too much, and now she wont leave the nest!

I will answer as it's surprisingly easy. You know you can actually teach yourself some of this stuff by just clicking "Record Macro", doing a few things and then looking at what the VB it created for you is like.

Basically you will want to do the following before the For Loop

Range("A2").Select ''This puts you back into A2 for the active cell.
Sheet1.Paste

And then this bit just after the exportRange.Select:

Application.CutCopyMode = False
Selection.Copy
Range("A2:A120").ClearContents ''This should really use some variables rather than hard-coded range. Bad practice!

That should do it. Now shoo! Go learn programming properly in C# or something.

 

[OCdude]

Thanks for this, I do appreciate it

I've just edited the macro, adding the extra bits - it 'kinda' worked once - I could see it quickly paste the data in, then it was cleared - however nothing was left in the clipboard.

Strangely, now when I run it, i get: Run-time error 1004 - Method 'Paste' of object '_Worksheet' failed an it highlights:

Sub SelectTillBlank()

    ''FIRST WE CREATE SOME VARIABLES WE NEED.
    
    Dim rowIndex As Integer ''This is where we keep the row we're looking at.
    Dim rowMax As Integer   ''This is how many rows we're going to keep searching for
    Dim colNum As Integer   ''This is the column we're going to search. A=1, B=2, etc.
    Dim exportRange As Range    ''This is where we're going to store our answer.
    
    ''NOW WE SET OUR DEFAULTS - COLUMN WE'RE SEARCHING, HOW LONG WE'LL SEARCH
    
    rowMax = 120            ''Lets assume less than sixty chapters (60x2)
    colNum = 5              ''This is Col. E
    
    ''NOW WE DO THE USEFUL STUFF

    Range("A2").Select ''This puts you back into A2 for the active cell.
[COLOR="Yellow"]Sheet1.Paste
[/COLOR]
    For rowIndex = 2 To rowMax                      ''This starts our loop down the column
        If (Sheet1.Cells(rowIndex, colNum) = "") Then    ''This shows we've found a blank space
            Exit For                                ''So because we've started finding blanks, we stop and exit the loop here.
        End If
    Next
    
    Set exportRange = Sheet1.Range(Sheet1.Cells(2, colNum), Sheet1.Cells(rowIndex - 1, colNum)) ''Now we set the range to be from the start to however far we got with RowIndex
    exportRange.Select  ''And now we tell that range to select itself.
    
Application.CutCopyMode = False
Selection.Copy
Range("A2:A120").ClearContents ''This should really use some variables rather than hard-coded range. Bad practice!

End Sub

I've renamed the sheet to "Sheet1" but it doesn't seem to make a difference. Also tried changing "Sheet1" to the actual sheet name, but that gave me a different error (object required). Sorry

 

[h4rm0ny]

Just seen your response. Hmmm. Try replacing "Sheet1" with "ActiveSheet". So you would have a line something like:

ActiveSheet.Paste

You should actually be able to use ActiveSheet in place of Sheet1 anywhere in the macro so long as you aren't operating on multiple worksheets. Sorry for bad info, if that was the problem.

Come to think of it, the error sounds like it doesn't have anything to paste. You say it worked once and then didn't the second time?

EDIT: Woops! That image was a bit bigger than I expected!

 

[h4rm0ny]

A second thought could be that because it's Excel and Excel can be funny like this, it's clearing the copied contents once you wipe the original data.

You could try moving the final ClearContents line back up to the very start just before you do the original paste call. So it wont be clearing it for the next sheet at the end, but at the beginning.