Using Offset and CountA to create a dynamic range in a chart

Peter Davison

Active Member
Joined
Jun 4, 2020
Messages
433
Office Version
  1. 365
Platform
  1. Windows
Hi,
I'm trying to set a dynamic range in a chart
My header is in cell E16 and my data can be in cells E17 to E517 or more
For this example my data is only 200 cells created from formulas in the cells so I have 300 blank cells with formulas in.
I don't know how to make the dynamic range ignore the formula blank cells?
Any help would be appreciated.
Thanks

=OFFSET('Start Data1'!$E$16,0,0,COUNTA('Start Data1'!$E$17:$E$517)-1,1)
 
No problem, I will sort the file out Sunday, can you just remind me how I upload an excel file?
I can see something on the menu bar that says XL2BB do I need to do something with that?
Thanks again for all your help.
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
XL2BB allows sharing portion of a worksheet directly on the forum.
To share a real workbook you need to upload the file to a filesharing server; the easiest to use is filedropper.com. You then must share di download link (that you will receive after the upload).

Bye
 
Upvote 0
Hi,
I haven't forgot I'm having to create a mock up file because the file it is working on is massive and contains loads of other data.
Bear with me.
Thanks
 
Upvote 0
While I am trying to set up enough data to send to you, could I ask in VBA code how would I copy only values derived from formula from a range of cells.
I've looked on Google, but can't find anything that is row specific, they seem to all be column specific

For example I have a row of data in a sheet in Cells E16 to AW16 all with formulas in the cells. Only Cells E16 to I16 have a value the rest of the cells are empty "".
I've tried an xl end to right type approach but there is other data on Row 16 further into the sheet.

I want to be able to copy E16:AW16 but only paste the cells that have data (E16 to I16) into a separate sheet.
Would you be able to advise?
Thanks very much for all your help, it is very appreciated.
 
Upvote 0
If you have a range of cells filled with formulas and wish to identify the rightmost column not empty, then you cannot use End (that will point to those columns having a formula or anyway having some data.

In this case my preferred suggestion is using Evaluate.
For example:
Code:
LastColInRange= Evaluate("=MAX((E16:AW16<>"""")*(COLUMN(E16:AW16)))")

This will return the column index of the rightmost cell in E16:AW16 that is not empty.

This works also with multirows range, for example:
Code:
LastColInRange= Evaluate("=MAX((E16:AW20<>"""")*(COLUMN(E16:AW20)))")
This will look for the rightmost column in range E16:AW20

Then you will use that colunm index to identify the range to copy.For example:
Code:
Range("Range("E16"), Cells(16,LastColInRange)).Copy


This is the last used column in the range, it doesn't mean that all the column at the left of this one are filled with data (not Empty).
If you wish to copy only those that contains data then you have to loop through the cells and process only those that are not empty; for example:
Code:
Dim myC As Range
For Each myC In Range("E16:AW16")
    If myC.Value <> "" Then
        'what to do if the cell is not empty
    End If
Next myC

Bye
 
Upvote 0
This is good thanks, I need to do a couple of other things in this procedure.
The - 'what to do if the cell is not empty
1. I have to paste the first selection result from your formula (from the Workbook(Clustering Graph Test File3.xlsm)Sheet(Start Data1) into another workbook (Reports.xlsm)/Sheet(Cluster Graph)Cell B7 for the first selection then ...
2. On a separate sheet in the original Workbook(Clustering Graph Test File3.xlsm) but on Sheet(Start Data2) I need to copy each one of those entries in E16 to AW16 until just all values are in Workbook(Reports.xlsm)Sheet(Cluster Graph) column B in the next cell under the initial entry in B7 then the loop goes back to the first procedure and gets the next value from sheet(Start Data1) and enters in the next available cell below the data and so on until all values are used.
Note - It is always taking only one at a time from Sheet(Start Data1) with all entries from Sheet(Start Data2). So they end up looking like this below -

Fro Cell B7 in Workbook(Reports)Sheet(Cluster Graph)
Wheat Cereals - From Workbook(Clustering Graph Test File3.xlsm)Sheet(Start Data1)
High Price - From Workbook(Clustering Graph Test File3.xlsm)Sheet(Start Data2)
Medium Price - From Workbook(Clustering Graph Test File3.xlsm)Sheet(Start Data2)
Low Price - From Workbook(Clustering Graph Test File3.xlsm)Sheet(Start Data2)
Biscuit Cereals - From Workbook(Clustering Graph Test File3.xlsm)Sheet(Start Data1)
High Price - From Workbook(Clustering Graph Test File3.xlsm)Sheet(Start Data2)
Medium Price - From Workbook(Clustering Graph Test File3.xlsm)Sheet(Start Data2)
Low Price - From Workbook(Clustering Graph Test File3.xlsm)Sheet(Start Data2)
Muesli - From Workbook(Clustering Graph Test File3.xlsm)Sheet(Start Data1)
High Price - From Workbook(Clustering Graph Test File3.xlsm)Sheet(Start Data2)
Medium Price - From Workbook(Clustering Graph Test File3.xlsm)Sheet(Start Data2)
Low Price - From Workbook(Clustering Graph Test File3.xlsm)Sheet(Start Data2)

etc. until all used.
Hope this is explained okay and again thanks for all your support, I really do appreciate your time.
 
Upvote 0
To copy from any A to any B you need to fully address A and B; for example:
Code:
Workbooks("SourceWorkbookA.xlsm").Range("TheInterval").Copy Destination:= Workbooks("DestinationWorkbookB.xlsm").Range("TheStartingCell")

This single line of commands will "copy" from A and "paste" to B

If you need to copy the values then you may use this other approach:
Code:
Workbooks("DestinationWorkbookB.xlsm").Range("TheBInterval").Value = Workbooks("SourceWorkbookA.xlsm").Range("TheAInterval").Value

The size of TheBInterval and TheAInterval need to be the same

For the other situations, it's difficult to me follow you without a sample workbook
 
Upvote 0
Thank you for your support I have now managed to sort out the code.
You have been really helpful and it is appreciated.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top