Posts by OldFella

After a few more hours of tearing my hair out I've made a bit of progress.

The '400' error pop-up was because measurements are taken twice daily so on the source worksheet the date was merged over two rows for each day. The merging has been eliminated, the date now shows on every row and the '400' error pop-up no longer occurs.

As a result I can successfully now copy filtered data to the summary sheet using:

Sub CopyVisibleRange()


    Range("A16:DK4000").SpecialCells(xlCellTypeVisible).Copy
    Sheets("Consolidated_Data").Range("A16").PasteSpecial (xlPasteValues)
       
End Sub

The area where I still require help is to amend that coding so that instead of being copied to a specific cell address it is copied to the first blank cell in col A below the existing data. I've been experimenting with End(xlUp) and End(xlDown) and Rows.Count and more but so far I've not been able to make it work.

Assistance with this will be much appreciated.

Hi graha_karya

Sorry - it was a long and tiring day yesterday so maybe I was too brief and didn't fully explain myself.

I have a worksheet with a mass of data that I can filter to show me the outcome from different parts of the process, but I have no way of seeing the the outcome from all the filters at the same time. The only way that I can think of, to enable me to see the total outcome, is to apply a filter and copy the visible cells to a sheet that I have called 'Consolidated_Data', and repeat this process for each filter.

So, for example, the first filter shows me:
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Filt01.png","data-attachmentid":1210741}[/ATTACH]
I have a Command Button - "Copy Cells to Cons_Data" - which I would hit and it would then copy that block of cells to 'Consolidated_Data'.

Then I change the filter settings and the new filter outcome is:
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Filt02.png","data-attachmentid":1210742}[/ATTACH]
I would hit the "Copy Cells to Cons_Data" button and the new block of cells would be copied to 'Consolidated_Data', beneath the first block of cells.

I reset the filter to a different part of the process and obtain a new filtered outcome (I can't show you a picture because we're limited to 2 x images).
I would hit the "Copy Cells to Cons_Data" button and the new block of cells would be copied to 'Consolidated_Data', beneath the second block of cells.

I keep repeating that process until I've applied each filter that I want to use, and on the 'Consolidated_Data' sheet I then have the combined output from all the filters. I can't show you a picture of what that would look like, firstly because I don't know how to code it to make it copy those visible cells from the source sheet to the 'Consolidated_Data' target sheet, and secondly because of the 2 x images limitation.

I already use existing coding that allows me to copy part of the unfiltered worksheet to a temporary back-up:

Sub BackUp()
     ActiveSheet.Range("E16:P4000").Copy Destination:=Worksheets("Temporary Data").Range("E16")
End Sub

I thought that by using similar coding, maybe I could copy the visible cells to another sheet, so I tried:

Sub CopyVisibleRange()
    ActiveSheet.Range("A16:DK4000").SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("Consolidated_Data").Range("A16")
End Sub

but it resulted in an error pop-up that just showed "400". I then tried adding Paste and PasteSpecial and several other things but I'm unable to make it copy the visible cells to the target sheet. I've scoured the internet to try to work out how to do it but I'm at a complete loss - hence my call for help.

Trust that clarifies it.

I have data on a worksheet that I need to filter in a number of different ways, and then for each filter setting I want to copy the range of visible cells to another worksheet - Consolidated_Data - however despite hours of trying I'm unable to make it do what I want.

I currently use:

ActiveSheet.Range("E16:P4000").Copy Destination:=Worksheets("Temporary Data").Range("E16")

to copy unfiltered data between two other sheets, and that works fine so I thought that maybe I could use a similar formula to copy the filtered data, however when I used:

ActiveSheet.Range("E16:DK4000").SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("Consolidated_Data").Range("A16")

I got an Error pop-up simply showing 400.

With only 1 line of code it's not hard to know which line is causing the problem, and it's seemingly because of the SpecialCells that I added in, so how do I correctly make it copy and paste the visible data?

Once I get the first block copied and pasted, I then want to reset the filter to a new set of conditions and then copy the new block of visible data to Consolidated_Data immediately below the first block. How do I incorporate this into the coding?

Hi Alan

Don't worry - I've solved it. After more searching I discovered a thing called IsNumeric, so what I now have is:
Option Explicit

Sub HigherThanF5()
    Dim crit As Variant
    Set crit = Range("F5")
    Dim c As Range, rng As Range
    Set rng = Range("K7:AH43")
    rng.Interior.ColorIndex = xlNone
    For Each c In rng
        If IsNumeric(c.Value) Then
            If c > crit Then
            c.Interior.ColorIndex = 4
            End If
        End If
    Next c
End Sub

I had to add in the line:

rng.Interior.ColorIndex = xlNone

because I found that when I changed the value in F5 it was leaving cells in K7:AH43 wrongly highlighted.

So now - all good. Thank you so much for your guidance. Between that, and my determination to try to work it out for myself, we've achieved the desired result. Another satisfied customer!

Hi Alan

It's giving a 'Type Mismatch' error pop-up.

Debug highlights the line:

Set crit = Range("F5").Value

so I went back to the previous line and tried various changes. By deleting ".Value", so that it became just:

Set crit = Range("F5")

it then correctly highlighted the cells in range K7:AH43 that were above the value in F5, but it also highlighted all the cells that contain text. This is the same problem that conditional formatting was giving me.

With 16 as the value in cell F5, here's part of the K7:AH43 range:

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"F5_Val.png","data-attachmentid":1210584}[/ATTACH]

The cells containing text are already formatted as text, as a result of the earlier conditional formatting problem.

Before posting this plea for help I'd noticed from extensive browsing, references to SpecialCells and xlNumbers. Does that somehow need to be added in, to ensure that only the cells containing numerical values are highlighted?

Thanks.

I'd appreciate some assistance with the following:

Range K7:AH43 contains a mix of cells with numbers and cells with text. I'm firstly trying to sum the highest numbers in the range to achieve a threshold total value, which I do in cell F6 with

=SUMIF(K7:AH43,">"&F5)

I put different numbers in F5 until I achieve the total value that I'm after. No problem up to that point.

However, once I establish the value in F5 that gives me the desired total, I then want to highlight all the numerical cells in range K7:AH43 that are above the value in F5. How do I achieve it? I've tried conditional formatting but it highlights text as well as numbers. I'd like to use VBA event coding that highlights the cells whenever F5 is updated - or by whatever other method achieves the result. Colourwise, I'm using the standard colour yellow.

Many thanks.

Re: VBA to Copy Visible Cells and Delete Blank Cells and Zero Values

Quote

So essentially you have filtered the data down to display a subset.

You want code to extract the values that are viewable whilst ignoring rows that have been hidden (filtered) and rows that contain nothing ("", vbnullstring).

Is that correct?

You're correct on both points, but in addition I also want to remove zero (0) values. The following screen shot shows a small section of the raw data, and the output that I'm looking for.
[ATTACH=CONFIG]73893[/ATTACH]

Quote

If so should it just start on row 16 or is that because that's the first visible row? (Not a good idea to hardcode something like that if it could change following different formatting or resorting of the data)

The data starts at row 16; rows 1 - 15 are used as a sort of control panel and contains headers, an assortment of subset results, and control buttons. I should note that the data cannot be sorted to get rid of the zeros and blank cells. The data is there in date order, running down the column, and the sequence needs to be maintained after the blanks and zeros are extracted.

Quote

Also, what should prompt it to end the extract of the data before outputting the results in AQ?

I'll assign the VBA coding to a control button so that I can run it as/when required.

I've realized that copying the col AF data to AQ16 may not be such a smart idea as the rows that are hidden as a result of the filter may introduce new problems. Instead it would seem better to copy col AF to the area below the filtered data. So, instead of starting the copied data at AQ16 it should now start at AQ1500. As before, the area to the right of col AQ is blank and can be used to hold temporary data if required.

I hope that clarifies it a bit better.

After filtering, range AF16:AF1150 of my worksheet consists of visible values, zero values and blank cells. Using VBA I want to copy the visible values within this range to col AQ, commencing at AQ16, so that I have just the visible non-zero values in a continuous sequence - no zero values and no blank cells. Can somebody please assist with VBA to achieve this.
If it assists, the area of the worksheet to the right of col AQ is blank and can be used to hold temporary data if required.
Thanks.

Re: How to Use a Cell Value as the Column Index in a VLOOKUP Formula?

Hi KjBox
Prior to posting the query I had actually tried that and found that it didn't work, but your suggestion to try it meant that it should work so I've tried it again. The result was initially puzzling.
The formula in K999 is basic: =23+(8*J999) so each increment in J999 moves me across the worksheet by 8 columns. I'd been doing all my pre-post testing with a value of 4 in J999, which equates to col 55. This returns a VLOOKUP error: #REF! yet the cell in col 55 (col BC) referenced by Q1001 contains the value 2.0.
However, if I reduce the J999 value to 3, the VLOOKUP formula works fine and returns, correctly, a value of 1.7. Similarly, a value of 2 returns the correct figure and so does a value of 1. But - once I use a value of 4 or greater in J999 I get the #REF! error.
I've just rechecked to ensure that the formula is giving the correct column index, which it is, however I've now found the source of the problem. Somehow, during my earlier testing, I had inadvertently changed the array in the VLOOKUP formula to $A$16:$AZ$1000 instead of $DZ$1000, so of course any column index above 52 returned an error.
Consequently, with some embarrassment I have to admit that the problem in fact isn't a problem, rather it's the result of my carelessness.
Thank you for the guidance, and for prompting me to go back and look more carefully at what I'd typed.

Is it possible, in a VLOOKUP formula, to use the value that is in a cell as the column index?
Specifically, I have a VLOOKUP formula in cell R1001: =VLOOKUP($Q1001,$A$16:$DZ$950,47,0). The formula is then repeated down column R for (currently) 283 rows.
I want to replace the "47" with the value that is in cell K999, that value in turn being derived from an underlying formula. The underlying formula is based on a value that I enter in cell J999. My objective is to be able to change the value in cell J999, which will then give a new, calculated value in K999, which in turn will then give me new output values from the 283 instances of the VLOOKUP formula in col R.

Re: Chart Using Dynamic Named Range Not Displaying As I Want It

Thanks arthurbr but unfortunately I couldn't find anything on the site to help me. None of the examples in any of the links duplicated my problem. Unfortunately I'm not simply dealing with empty cells that will have data inserted at some later point, which will then dynamically expand the chart. My cells contain an underlying formula and even though the cells appear blank, because the data that will cause the formula to then display a value hasn't yet been entered, the underlying formula is being interpreted as a zero and the chart displays a long line of zero values.

I'm attempting to generate a chart by using dynamic named ranges. The column of data that makes up the named range has cells displaying values and then below those are cells that do not yet display values but do contain an underlying formula. The problem I am facing is that the lower cells, the ones that contain a formula but do not yet display a value, are displaying on the chart as 0 values.
How do I overcome this, so that the chart only displays the preceding, non-zero values?

In case of need, the dynamic named range Refers to: =OFFSET('D_RT'!$M$61,0,0,COUNTA('D_RT'!$M$61:$M$200),1). Currently the range M61:M83 contains displayed values and the range M84:M200 has a formula in every cell but there is no displayed value.

Re: VBA to Automatically Increase the Last Used Value in a Column

Fantastic! - you guys ROCK! Carim, KjBox - thank you both for the assistance. I now have worksheets with both the original and the new event coding, and both modules play happily together. Perfect!

Re: VBA to Automatically Increase the Last Used Value in a Column

Hi Carim
Thank you so much for your perseverance; it's gratifying to have it now working correctly. Could I ask for one final piece of help - I still need to combine it with the coding that capitalizes cell P3 and column X, so that I end up with coding similar to your #15 post.

Hi KjBox
In most situations the data is simply entered in a sequence across the row and never altered, however I do have one worksheet where the data is regularly changed and the col AE cell value likewise changes, so your coding will be very useful for that sheet. Could I ask the same of you as I just did of Carim - as with other sheets, that one also has existing event coding that capitalizes cell P3 and column X, as per my post #12. Could you combine your coding and the existing coding into a single block so that both macros will work happily together.

Again - thank you both.

Re: VBA to Automatically Increase the Last Used Value in a Column

Hi Carim
Sorry this is proving to be so troublesome. I made the change as you suggested. It has eliminated the error pop-ups, but it's still not giving the update to column A when the value in col AE changes.
In case it helps you I've attached a section from the worksheet. I'm using row 23 to test the coding. The display is currently correct; cell A23 should be blank because the value in AE23 is 0, so A23 does not need to be updated. Now - if you change cell X23 from "B" to "S" you will see that AE23 changes from 0 to -0.1, so A23 should now update and show "X2" - but it doesn't. Or - change X23 back to "B" again, and then change AC23 from 1281.66 to 1291.66. AE23 will now display -1.0, which should cause A23 to update and display "X2" - but it doesn't.
See if that helps you.

Re: VBA to Automatically Increase the Last Used Value in a Column

Hi Carim
The first thing I tried, after making that change, was to change the value in cell P3. That gave a "Run-time error '424': Object required". Running the debugger highlights the first line of code: If Target.Count > 1 Then
Next, in order to limit the trial to just the coding that relates to col AE I commented out the coding that merges the two event macros (#15) and went back to your initial coding (#11) and replaced the first line. Now, as soon as I enter any value, anywhere, I get that same error pop-up and the same result when I run the debugger.

Re: VBA to Automatically Increase the Last Used Value in a Column

Hi Carim
My apologies - I posted a response to your updated coding yesterday, advising that the combined coding worked well, but I obviously did something wrong as it's not displaying. However, I have a new issue.
After a day of using the worksheet, a problem has arisen. If I manually input a value into column AE then column A updates correctly, however in practice the value in AE is not manually input, it's derived from a formula that takes its data from the preceding 10 - 12 cells in the row. I'm guessing that this, presumably, does not constitute a change event. If that's the case, can the coding be modified to behave as described in the earlier post(s) but to be based the output value of a formula, not on a manually input value?
In case it's relevant, the sequence of events as data is entered across the row is:
a) the column AE cell is initially blank;
b) data is entered into columns J, P, Q, R, T and W and the AE cell remains blank;
c) data is entered into column X and the AE cell displays 0;
d) data is entered into columns Y, Z, AA and AB and the AE cell continues to display 0
e) data is entered into column AC and the AE cell displays a changed value;
f) data is entered into column AD and the AE cell displays its final value.
Data is always entered in sequence across the row, so as I see it the value finally displayed in column AE after entering data into column AD is the change event that needs to trigger the update to column A.
I apologise that this is somewhat more involved than I first described, but I simply hadn't understood the distinction between a manually input value and a formula-derived value.

Re: VBA to Automatically Increase the Last Used Value in a Column

Thanks Carim. I've only applied your coding to one worksheet so far and, as I said, it works perfectly there. I was planning to extend it to the other sheets tomorrow so I'll take note of your guidance to add it to ThisWorkbook. At present I have it in the worksheet's module. Thanks again for your on-going guidance.

Re: VBA to Automatically Increase the Last Used Value in a Column

Hi Carim
I 've just trialled the coding on one page and no matter what number I have in col AE - it works perfectly! But - I've become accustomed to that by now! Thank you once again.

One sheet that I'd like to use that coding on already has event coding on it, to capitalize the alpha characters that are entered in two specific ranges. Is it possible to combine your coding with the existing coding so that they both work? The coding that's currently there is:

Private Sub Worksheet_Change(ByVal Target As Range)
' This module capitalises entries in the specified column(s) or cell
Dim changed As Range, c As Range
Dim cVal
Const myR As String = "P3:P3,X16:X500" 
Set changed = Intersect(Target, Range(myR))
If Not changed Is Nothing Then
Application.EnableEvents = False
For Each c In changed
cVal = c.Value
Select Case True
Case IsEmpty(cVal), IsNumeric(cVal), _
IsDate(cVal), IsError(cVal)
' Do nothing
Case Else
c.Value = UCase(cVal)
End Select
Next c
Application.EnableEvents = True
End If
End Sub

Re: VBA to Automatically Increase the Last Used Value in a Column

Hi Carim
It's just a single letter, so on the first sheet it's from A1 onwards, the next sheet from B1 onwards, and so on. As noted, on the currently active sheet the ID values range from D1 to D127, although there are actually 159 rows of data. 32 rows either have a "0" in col AE or are blank, leaving 127 rows that required an ID number.
On any given sheet there is just a single alpha character forming part of the ID, and that character is unique to that sheet.
Hope that clarifies it.