Adding up to 10 rows of data, but automatically ignoring blank rows

[buffyisveryfuzzy]

Hello all, I am new to this form and wish I had joined awhile back.

If we were to rank my Excel skills by the United States Class system, I would probably be cresting Upper-Middle class; so very good, but not an expert yet.

I have been working on a project for work that my coworkers are very impressed with, however, I’ve run into an issue when it comes to ignoring blank rows.

Let me break it down for you.

There are 5 columns of data, from left to right as follows:

‘Date and Time’ – ‘Staff Member’ – ‘Hospital’ – ‘Topic’ – ‘Notes’

‘Date and Time’ automatically updates to the current date and time, but the next three columns have dropdowns so the user is limited to the data they can input. Finally, the notes section has no limitations.

Underneath those column topics there are 10 rows to allow for bulk data entry. If the user only has one rows worth of information, they would use only the top row, hit the ‘SUBMIT’ button, and their entries would be deleted from the current sheet and transferred to a ‘Database” sheet for storage; there’s even a message that pops up stating that their data was successfully transferred.

For a user that wants to enter 10 rows of data, they would hit the button that says ‘BULK SUBMIT’ and exactly the same as above would happen for all 10 rows.

For both instances, the new data is inserted as new rows at the top of the ‘Database’ tab, pushing the existing data down to keep all data in chronological order.

Now the problem with the ‘BULK SUBMIT’ macro.

No matter how many rows the user fills in, even if it’s not all of them, ALL 10 ROWS will transfer to the ‘Database’ tab. So if the user fills in the top 2 rows and hits ‘BULK SUBMIT,’ the macro will function, but there will be two new rows of data at the top of the ‘Database’ Sheet followed by 8 blank rows underneath that, followed by the older rows that were entered previously.

I want a user to be able to hit one ‘SUBMIT’ Button, whether it’s for a single entry, 2 entries, 6 entries, or all 10 entries; which I can easily make happen.

HOWEVER

I want the Macro to recognize blank rows and omit them from being transferred to the database.

So if I decided to enter data into row 1, 2, 6, and 9 but nothing in the others, the macro behind the ‘SUBMIT’ Button will ignore rows 3, 4, 5, 7, 8, and 10 and move 1, 2, 6, and 9 (IN THAT ORDER) TO THE ‘Database’ Sheet.

Am I making since? I feel like this is a very doable thing.

Please, all and any help is appreciated.

Thank You

 

[Eric W]

Welcome to the forum.

Yes, that is very doable. However it's very dependent on how your code already works. Could you post the code to the BULK SUBMIT macro (cleaning out any sensitive data)?

 

[buffyisveryfuzzy]

Currently the code behind the 'Bulk Submit' button looks like this:

Sub BulkSubmit()
'
' BulkSubmit Macro
'


'
    Range("A10:O19").Select
    Selection.Copy
    Sheets("Database").Select
    Range("A5:O5").Select
    Selection.Insert Shift:=xlDown
    Sheets("Data Entry").Select
    Range("D10:N19").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
    MsgBox "Your data has been removed from here and transfered to the Database Tab"
    
End Sub

 

[Eric W]

This could work for you. I looked at column D to decide if the row should be moved. If that's not the case, then change the parts marked in red. Also, the columns that are being copied and then cleared are not the same, so take special note of the parts marked in blue. The starting row on the Database sheet is named TopRow. Try this with a sample sheet. Let me know how it works.

Sub BulkSubmit()
Dim r As Long, c As Long, r1 As Long, MyData As Variant, ctr As Long, TopRow as long
'
' BulkSubmit Macro
'
    Application.ScreenUpdating = False
'
    MyData = Sheets("Data Entry").Range("A10:O19")
    ctr = WorksheetFunction.CountA(Sheets("Data Entry").Range("D10:D19"))
    If ctr = 0 Then
        MsgBox "No data found"
        Exit Sub
    End If
    Sheets("Data Entry").Range("D10:N19").ClearContents
    Sheets("Database").Select
    TopRow = 5
    Rows(TopRow & TopRow + ctr - 1).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    r1 = TopRow
    For r = 1 To 10
        If MyData(r, 4) <> "" Then
            For c = 1 to UBound(MyData, 2)
                Cells(r1, c) = MyData(r, c)
            Next c
            r1 = r1 + 1
        End If
    Next r
    Range("A5").Select
    
    Sheets("Data Entry").Select
    Application.ScreenUpdating = True
    
    MsgBox "Your data has been removed from here and transfered to the Database Tab"
    
End Sub

 

[buffyisveryfuzzy]

Beautiful, it works as I described it! Thank You, now I'm trying to reverse engineer it to see how its working exactly.

One minor detail however. I am unsure of what the "Submit" Macro has that the "Bulk Submit" doesn't, but the formatting isn't being copied with the data.

The single submit macro VB code looks like this:

Sub Submit()
'
' Submit Macro
'

Range("A10:O10").Select
Selection.Copy
Sheets("Database").Select
Range("A5:O5").Select
Selection.Insert Shift:=xlDown
Sheets("Data Entry").Select
Application.CutCopyMode = False
Range("J10:N10").Select
Selection.ClearContents
Range("H10:I10").Select
Selection.ClearContents
Range("F10:G10").Select
Selection.ClearContents
Range("D10:E10").Select
Selection.ClearContents

MsgBox "Your data has been removed from here and transferred to the Database Tab"

End Sub

Would you know whats preventing the formatting of the table in the 'Database' Tab when the data gets copied in?

 

[Eric W]

Sorry, I didn't realize you wanted the formatting too. Try this version (critical parts marked in red again):

Sub BulkSubmit()
Dim r As Long, r1 As Long, ctr As Long, TopRow As Long
'
' BulkSubmit Macro
'
    Application.ScreenUpdating = False
'
    ctr = WorksheetFunction.CountA(Sheets("Data Entry").Range("D10:D19"))
    If ctr = 0 Then
        MsgBox "No data found"
        Exit Sub
    End If
 
    Sheets("Database").Select
    TopRow = 5
    Rows(TopRow & ":" & TopRow + ctr - 1).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    r1 = TopRow
    For r = 10 To 19
        If Sheets("Data Entry").Cells(r, "D") <> "" Then
            Sheets("Data Entry").Range("A" & r & ":O" & r).Copy Sheets("Database").Range("A" & r1)
            r1 = r1 + 1
        End If
    Next r
    Range("A5").Select
    
    Sheets("Data Entry").Select
    Sheets("Data Entry").Range("D10:N19").ClearContents
    Application.ScreenUpdating = True
    
    MsgBox "Your data has been removed from here and transferred to the Database Tab"
    
End Sub

 

[buffyisveryfuzzy]

Amazing, thank you so much!

 

[Eric W]

You're welcome!

Incidentally, you can shorten your Submit macro to:

Sub Submit()
'
' Submit Macro
'
    Sheets("Database").Rows(5).Insert shift:=xlDown
    Sheets("Data Entry").Range("A10:O10").Copy Sheets("Database").Range("A5")
    Sheets("Data Entry").Range("D10:N10").ClearContents

    MsgBox "Your data has been removed from here and transferred to the Database Tab"

End Sub

It looks you you created it using the Record Macro tool, which is a fantastic help, but doesn't always generate neat code.

 

[buffyisveryfuzzy]

Actually, I do have one final request if you don't mind. The data that is transferred is selected by the user from a drop-down to keep things in order. However, once it gets to the 'Database' Tab the drop-down isn't necessary and I believe its preventing me from graphing some of the data. How would you write the code to move only the selected values vs. the whole drop-down with the value selected,m if that makes sense.

 

[Eric W]

Try these:

Sub Submit()
'
' Submit Macro
'
    Sheets("Database").Rows(5).Insert Shift:=xlDown
    Sheets("Data Entry").Range("A10:O10").Copy
    Sheets("Database").Range("A5").PasteSpecial Paste:=xlPasteValues
    Sheets("Database").Range("A5").PasteSpecial Paste:=xlFormats
    Sheets("Data Entry").Range("D10:N10").ClearContents

    MsgBox "Your data has been removed from here and transferred to the Database Tab"

End Sub

Sub BulkSubmit()
Dim r As Long, r1 As Long, ctr As Long, TopRow As Long
'
' BulkSubmit Macro
'
    Application.ScreenUpdating = False
'
    ctr = WorksheetFunction.CountA(Sheets("Data Entry").Range("D10:D19"))
    If ctr = 0 Then
        MsgBox "No data found"
        Exit Sub
    End If
 
    Sheets("Database").Select
    TopRow = 5
    Rows(TopRow & ":" & TopRow + ctr - 1).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    r1 = TopRow
    For r = 10 To 19
        If Sheets("Data Entry").Cells(r, "D") <> "" Then
            Sheets("Data Entry").Range("A" & r & ":O" & r).Copy
            Sheets("Database").Range("A" & r1).PasteSpecial Paste:=xlPasteValues
            Sheets("Database").Range("A" & r1).PasteSpecial Paste:=xlPasteFormats
            r1 = r1 + 1
        End If
    Next r
    Range("A5").Select
    
    Sheets("Data Entry").Select
    Sheets("Data Entry").Range("D10:N19").ClearContents
    Application.ScreenUpdating = True
    
    MsgBox "Your data has been removed from here and transferred to the Database Tab"
    
End Sub