WildBurrow
New Member
- Joined
- Apr 5, 2021
- Messages
- 38
- Office Version
- 365
- Platform
- Windows
The worksheet in question is NOT a table. Some of my fields are merged and I am fully aware that I need to unmerge to copy/paste then merge after the code runs. My worksheet contains two sections which are restricted to a set number of rows for data entry.
1) Responsible Party Analytical Processes (rows 4:7) and,
2) Methods (rows 11:12, rows 14:15, rows 17:18, and rows 20:21)
Because there are field references below these two sections that must remain where they are, the xlShiftUp is not an option. The first row of each of these sections remains visible at all times, while the three subsequent rows are hidden. The first row requires user input unless they select an associated checkbox that populates "N/A". In addition to having rows hidden until called upon, I have the option of deleting a row of data when selected. The code for these two features is clunky....I use exact cell references and the code is very long. This is a portion of the code for the Methods section (cell references are from the actual workbook vs a scaled down version created for L2BB);
I envision:
I'm looking for assistance with looping and finding next empty cell/row so I can move data up. Thank you for your consideration.
1) Responsible Party Analytical Processes (rows 4:7) and,
2) Methods (rows 11:12, rows 14:15, rows 17:18, and rows 20:21)
Because there are field references below these two sections that must remain where they are, the xlShiftUp is not an option. The first row of each of these sections remains visible at all times, while the three subsequent rows are hidden. The first row requires user input unless they select an associated checkbox that populates "N/A". In addition to having rows hidden until called upon, I have the option of deleting a row of data when selected. The code for these two features is clunky....I use exact cell references and the code is very long. This is a portion of the code for the Methods section (cell references are from the actual workbook vs a scaled down version created for L2BB);
VBA Code:
'Current Report Activity
'Must use 1st remediation section before opening a second
If ws1.range("B165").Value = "" Then
MsgBox "You must complete the first remediation" _
& vbNewLine & "section before adding a second.", vbOKOnly + vbExclamation, "Remediation #1 Empty"
ElseIf ws1.range("B165").Value <> "" Then
'Show section if not used (default settings)
If ws1.range("X271").Value = "True" And ws1.range("S167").Value = "" Then
With ActiveSheet
Rows.EntireRow("166:168").Hidden = False
.range("X271").Value = "False"
End With
'Hide section if not used (default settings)
ElseIf ws1.range("X271").Value = "False" And ws1.range("S167").Value = "" Then
With ActiveSheet
Rows.EntireRow("166:168").Hidden = True
.range("X271").Value = "True"
End With
'Hide section if added on current report but offer warning message about info being deleted
ElseIf ws1.range("X271").Value = "False" And ws1.range("S167").Value = "Yes" Or ws1.range("S167").Value = "No" Then
Answer = MsgBox("Section contains data! Do you want to remove" _
& vbNewLine & "this section and delete the information?", vbYesNo + vbCritical, "Delete Remediation #2")
If Answer = vbNo Then Exit Sub
If Answer = vbYes Then
MsgBox "If you have information in subsequent" _
& vbNewLine & "remediations sections, the information" _
& vbNewLine & "will move up to the next available row.", vbOKOnly + vbInformation, "Existing Date Will Move Up"
'Prevent msgboxes offering Remediation 3 or 4
Application.EnableEvents = FalseI envision:
- a single command button/icon that will allow the user to unhide the next empty row within the section, if criteria is met (loop and find next empty row/cell?)
- a single command button/icon that will move data up when an entry is deleted within the section (loop and find next empty row/cell?)
- I will always want all blank rows to be at the bottom of each section and hidden until called upon (sort?)
I'm looking for assistance with looping and finding next empty cell/row so I can move data up. Thank you for your consideration.
| Book1 | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | INCIDENT INITIAL REPORT | |||||||||
| 2 | Remediation Activity | |||||||||
| 3 | Responsible Party Analytical Processes Date Description | |||||||||
| 4 | 03/15/24 | Water sent to DiStematic for testing | ||||||||
| 8 | ||||||||||
| 9 | Methods | |||||||||
| 10 | Remediation Methods and Processes Title #1 | Start Date | Completed? | Date Complete | Documents? | |||||
| 11 | Specify Subject | |||||||||
| 12 | ||||||||||
| 22 | ||||||||||
| 23 | Mitigation Actions | |||||||||
| 24 | Corrective Actions - Steps to prevent reoccurrence | |||||||||
| 25 | ||||||||||
| 26 | . | |||||||||
| 27 | ||||||||||
Sheet1 | ||||||||||
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| E11 | List | Yes, No |
| G11 | List | Yes, No, N/A |
