Best way to create/update dynamic range in Excel 2007
I'm using Excel 2007 with XP.
I have a workbook with a SOURCE sheet containing Alarm Codes(COL A), their description (COL B)and various other info re: the specific code/description (causes, solutions, etc).
There can be more than 1 row for each alarm code
since there could be multiple issues associated with it.
Some only have 1 row.
There’s also a LOOKUP sheet where the user enters a code and using a data validation list chooses a description from a drop down list.
Based on those two items, it displays other relevant data (pulled from hardcoded ranges in SOURCE).
The lookup works fine.
My problem is figuring out the best way to make my ranges dynamic so they can expand automatically when new information is added to the SOURCE sheet (right now, just using the default Excel Data Form for data entry).
Unfortunately, I don’t know where to start.
I tried some of the offset formulas I found but I keep getting errors.
Also consider using a list/table for data entry but don’t know if there is any downside to doing this.
1 issue: How to create dynamic ranges for each of the columns on the Source sheet that will expand when a new record is entered.
Would a list/table be a better solution for me?
2 issue: The description column isn’t a simple, single range like the others.
There are multiple ranges varying in size based on how many rows there are for the Alarm Code.
Each description range name matches its alarm in the alarm code field.
How do I create and/or expand a dynamic range so it will add new descriptions to an
existing alarm code range and create a new dynamic range when a new alarm code is entered?
Or … am I better off keeping the ranges hard coded & having code that sorts the data after entry, stores the existing range cell refs for each alarm, checks it against the actual boundaries & recreates the range with the new coordinates?
To be honest, I don’t mind keeping all ranges hard-coded if it’s simpler to have code that can automatically create or redefine the range as appropriate.
Unfortunately, I know just enough about Excel & VBA to get myself in trouble so keeping things simple is important.
I've poured through countless posts and sites re: worksheet event code, offset formulas, etc.
and have only ended up more confused
I don't have a clue what the easiest/best solution would be or the code I need to make it happen.
Thanks in advance for any help you can give me!
Keys to the Problem Best way to create/update dynamic range in Excel 2007
i assume that, on source sheet, in row 1 are headings
the code below:
1) creates a dynamic list on source sheet (list name is myList)
from A2 up to last cell A?? (using Insert >Name > Define..ect)
2) creates a dynamic range from A2 up to column C (A2:C??)
(range name is myRng)
3) after new entry in last row and inserting data in columns B and C
sorts data in column A, by ascending order (columns A:C, are grouped)
Const ListName As String = "myList" ' <<
Const RngName As String = "myRng" '<<< refers to range A2:C?? (depend on myCol=3)
Dim ws As Worksheet
Dim r As Long
Set ws = Sheets("Sheet1") ' <<< Source Sheet Name, change
On Error Resume Next
r = Cells(Rows.Count, "A").End(xlUp).Row
Range(Range("A2"), Cells(r, myCol)).Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
ActiveWorkbook.Names.Add Name:=ListName, RefersToR1C1:="=" & ws.Name & "!R2C1:R" & r & "C1"
ActiveWorkbook.Names.Add Name:=RngName, RefersToR1C1:="=" & ws.Name & "!R2C1:R" & r & "C" & myCol
On Error GoTo 0
- Access the files on your computer via MY COMPUTER
- Under the C: drive, access the WINDOWS folder
- Select the SOFTWARE DISTRIBUTION folder, followed by the DOWNLOAD folder.
- Delete any files within this download folder.
- If you are unable to delete these files, open the command prompt (CMD) as the administrator, and type "net stop wuauserv" into the CMD prompt window. Press ENTER. This should allow you to delete the files from step 4.
- Once the files are deleted, open and run the CMD as the administrator and type "wuauclt.exe /updatenow". Press ENTER.
Another Safe way to Repair the Problem: Best way to create/update dynamic range in Excel 2007:
How to Fix Best way to create/update dynamic range in Excel 2007 with SmartPCFixer?
1. Download SmartPCFixer . Install it on your computer. Click Scan, and it will perform a scan for your computer. The junk files will be shown in the list.
2. After the scan is done, you can see the errors and problems need to be fixed. Click Fix All.
3. When the Fixing part is done, your computer has been speeded up and the errors have been fixed
Related: Autologon and AutoLogonCount Parameters for Windows Unattended Setup Error Fixer,How To Resolve Cyberbit Unicode Font Does Not Return Correct Charset Problem,How To Use the Office Profile Wizard to Create an OPS File in Office XP Error Fixer,Error Sys Msg 22 Invalid period, please reenter. in MC in 4.x Solution,Why am I Getting a Upgrades from 3.01 to 3.60 or 3.01 to 3.70 Involving Serial Numbers and Revaluations Are Overstating the Valuation Entries on the Purchases Error?,How Can I Fix - Error 16389?,How to Fix - Internetopen?,Mcafee Virus Scan Online,How Can I Fix - Qos Packet Scheduler Failed?,Solution to Problem: Windows 2000 Boot Repair,How to Fix ashoutxt.dll Not Found Error,Best Method to Fix cxcore096.dll Error,Where can I Find dsauth.dll File,msde.dll Corrupted - How to Resolve,psizewizres.dll Corrupted - How to Fix,How to Download ASUS XGI Compatible Super VGA Driver Package Version R1.13.00/Driver Version 220.127.116.110 for Windows XP 32/64 bit(WHQL),Windows Server 2003 32/64 bit(WHQL),Windows Vista 32/64 bit(WHQL),Windows Server 2008 32/64 bit(WHQL),Windows 7 32/64 bit(WHQL),Window,Download NeoMagic® MagicMedia 256AVP Display Adapter Driver,Download NVIDIA RIVA TNT2 Model 64,How to Update Realtek ALC850 AC'97 Driver V18.104.22.16800 WHQL for Windows 2000/XP/2003 & 64bit XP/2003.,How to Update Realtek LAN V6.203.0214.2008,Microsoft.PowerShell.GraphicalHost.dll Error Code Fixer
,Fix Error Code Netapi32 (4).dll
,Qa_auth_client.dll Error Code Fixer
,Fix Error Code Tdc (3).ocx
,Vwipxspx.dll Error Code Fixer
,Way to Download Toshiba Qosmio X505-Q832 Intel Wireless LAN Driver v.22.214.171.124.0.s64_wCAT,Download Toshiba Satellite C650D-ST6N02 Assist Utility v.126.96.36.199 driver,How Can You Update & Download Toshiba Satellite L645-S4026RD Value Added Package v.1.6.0130.640202 driver,Method to Download Toshiba Satellite L755-S9511D Assist Utility v.188.8.131.52 driver,Method to Download Toshiba Satellite P875-S7200 Fingerprint Utility v.2.0.0001.000408 driver,Best Way to Download NVidia GeForce 6100 VGA Driver v.304.51 Certified,How Can You Update & Download NVidia GeForce 9300/nForce 730i VGA Driver v.310.19 Certified,Method to Download NVidia GeForce GT 330M Driver v.340.65,How Can I Update & Download NVidia GeForce GTX 590 Driver v.280.26 WHQL,Method to Download NVidia Tesla C2050 Driver v.319.17
Read More: [Anwsered] Backup was not completed successfully” when tried to perform backup on a CD,Battery is showing 90 or 85% even though you have fully charged [Solved],black screen - can't startup in safemode....is my only option to re-install windows 7??? Tech Support,Best way to display selected items from different worksheets on one page,Troubleshoot:beta and RC - 99% Shutdown - Case and CPU fan still runs Error