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

 

[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).

 

[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!

 

[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!

 

[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