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

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
133
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)
 

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
133
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,928
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
 

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
133
Office Version
  1. 365
Platform
  1. Windows
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
 

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
133
Office Version
  1. 365
Platform
  1. Windows
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.
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,928

ADVERTISEMENT

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
 

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
133
Office Version
  1. 365
Platform
  1. Windows
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.
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,928
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
 

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
133
Office Version
  1. 365
Platform
  1. Windows
Thank you for your support I have now managed to sort out the code.
You have been really helpful and it is appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,116
Messages
5,546,035
Members
410,721
Latest member
adi772
Top