excelvbaexcel-formula

Copy data up until last data entry in a specific column of choice, and paste to another sheet? Is there a way to ignore Blanks when using formulas?

Q1: I have written a VB script that works, but would like it to be more dynamic.

My Script is as follows

Sub UpdateMaster()

Dim ws As Worksheet
Dim tracker As Worksheet
Dim r As Range

    Application.ScreenUpdating = False
    
    If MsgBox("Would you like to update the tracker?", vbYesNoCancel) = vbYes Then

   Set ws = ThisWorkbook.Sheets("Bulk") 'data set where users input the info
   Set tracker = ThisWorkbook.Sheets("TRACKER") 'master data set where all user data entries become added
    
    Set r = ws.Range("A1").CurrentRegion.Offset(1) ' removes headers
    
            r.Copy Destination:=tracker.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) 
    'pastes to first available row in tracker sheet
            
            MsgBox "Tracker Updated"
        
        Else
            
            MsgBox "Process Cancelled"
            
        End If

    Application.ScreenUpdating = True

End Sub

I have pre-set formulas in columns A/D/E/F/G/J (refer to as extracted info), and also a reset macro where the manual data points inputted in columns B/C/H (refer to as manual) become cleared. Say rows 1 - 10 have data that is entered in the manual columns, the data then shows up in the extracted info columns likewise. But because I've filled the formulas to row 1000, if I use my VB script UpdateMaster, it will copy everything until row 1000 and paste to the tracker worksheet - essentially the excess blank cells with formulas in them.

If I want it to copy the contents up until the last data point entered in column C, but still include the data in column A, which part of the code do I revise?

Example: Column C Row 10 has last input of data, copy all contents up until row 10. (Even though there is formulas in cells up until row 1000)

Q2: Can blank cells be ignored in formulas for a specific case? On a separate worksheet, I have a list of all data - and this is the information that is pulled with the formulas on the data logging sheet. These formulas (basic index/match) are contingent on data in column C. =IFERROR(INDEX(Sheet1!G:G,MATCH(CONCATENATE(C2),Sheet1!A:A,0)),"Not Found").

On the worksheet "sheet1" that has all the data, there are some cells in column A that are blank where the formula is trying to match that info - while there is still data within that row except for A, and the formula finds a match.

So, I'd like to ignore those small cases, because the formulas are showing a solution or finding a match when in reality I just have those cells blank for when I want to put info later. It reads the blank cell as data that matches my index/match. Again, the formulas are just down until 1000, so if i only input actual data up until row 10, the data in the latter will show a match even though i didn't input any data and its blank.

Solution

  • Q1
    replace:

    Set r = ws.Range("A1").CurrentRegion.Offset(1) ' removes headers

    with:

    c_l = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
Set r = ws.Range("A2:J" & c_l)

    (remove all Dim statements or include Dim c_l as long as you prefer).

    Q2

    pre-requisite: 365 compatible excel.

    =LET( x_, TEXTJOIN("", 1,C2),IF(x_="", "",IFERROR(INDEX(A:A,MATCH(x_,TEXT(G:G,"0"),0)),"n/a")))

    notes:

    ̿̿ ̿̿ ̿’̿’\̵͇̿̿\З= ( ▀ ͜͞ʖ▀) =Ε/̵͇̿̿/’̿’̿ ̿ ̿̿ ̿̿ ̿̿