Copy/Paste data to next empty row within a range - WITHOUT shifting subsequent rows up

WildBurrow

New Member
Joined
Apr 5, 2021
Messages
38
Office Version
  1. 365
Platform
  1. 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);

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 = False

I 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 am have difficulty with syntax on setting a range, finding the next blank cell/row, then offsetting that address to identify all the cells to be copied and pasted. So for instance, under 'Responsible Party Analytical Processes', I can locate for the next blank cell in column B, but when I offset to the next row below that contains data, I can't seem to write the code that will copy data from column B and C and move up to that blank row. Writing code for the 'Methods' section is a bit more complex because each method has six (6) fields over two rows.

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
ABCDEFGH
1INCIDENT INITIAL REPORT
2Remediation Activity
3Responsible Party Analytical Processes Date Description
403/15/24Water sent to DiStematic for testing
8
9Methods
10Remediation Methods and Processes Title #1Start DateCompleted?Date CompleteDocuments?
11Specify Subject
12
22
23Mitigation Actions
24Corrective Actions - Steps to prevent reoccurrence
25
26.
27
Sheet1
Cells with Data Validation
CellAllowCriteria
E11ListYes, No
G11ListYes, No, N/A
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

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