Replace exact number of deleted rows to maintain the same number of rows in the sheet ?

[netrixuser]

Hi All, I'd appreciate your help in approaching my current issue - sort of Userform related initially but my lack of knowledge has pushed me to the vba route.
Trying to summarise:
I have a spreadsheet with data in columns A through P and Rows 4 to 26.
Following a Youtube tutorial I have managed to construct a userform that populates the sheet (one row of data at a time) it will populate the data into the next free row of the sheet each time. I also managed to get an Edit option whereby the userform pulls the data from the sheet and writes it back the changes back to the original row of data in the sheet.
My problem is that some of the rows will need to be deleted before others and those deleted rows could be anywhere in the sheet. Ideally I would like a option in the userform to pull back data from the sheet and delete it from there but I had no clue on how to go about this.
My Userform it is written such that it references an "anchor point" at the top of the data in column A to move down and across, and uses the result of a CountA for cells A5:A26 to know where the next blank row is [CountA plus 1]
So, with my limited knowledge, I added a column in the sheet (Column Y) for the user to enter a Y in the corresponding row that is to be deleted, and then call a Macro via a button on the sheet to delete those rows. I "borrowed" the code below from t'internet to move down and delete the entire row if there is a Y in the column.

But I need the overall number or rows to be constant - ie to count the rows deleted and insert them back - any ideas please on how to achieve this ?

Many thanks for your time.

Sub DeleteCells2() 
 Dim rng As Range     
 Dim i As Integer,  counter As Integer  
    Set rng = Range("P5:P26") 
    i = 1  
    For counter = 1 To rng.Rows.Count        
    If rng.Cells(i) = "Y" Then                
      rng.Cells(i).EntireRow.Delete            
    Else                
      i = i + 1            
    End If         
Next 

End Sub

 

[mumps]

It would be easier to help if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[netrixuser]

So here goes - my Userform data, called by the Add Data button shown below, will use a countA formula for range A5:A26 and add 1 to that number. With that number it will count down column A from an anchor point in Cell A4 - therefore entering the data in the next available blank row.
If I clear the entries from rows 5 and 6, for example, (highlight cells and Clear Contents) my countA formula would equal 3 and so place the next userform entries in row 7, which is not blank.

So I would like to either write better code to ascertain where the next blank row is (in the example given above, if rows 5 and 6 are blank and rows 7 and 8 contain data, it will be ok for the next two entries to be in rows 5 and 6) or, as per the original, badly worded, post: write a macro that when run, it will delete, say rows 5 and 6, but also move the rest of the data up to occupy those now blank rows. The sheet could possible contain data in rows 5 through 26 (so 22 rows) and that needs to remain constant so that the table always has rows 5 through 26 available for data input.

Hope that all made better sense ! probably not, but worth another try methinks

 

[mumps]

It is hard to work with a picture. Also, I cannot see your userform nor the code you are currently using. Please follow the instructions in Post #2 to attach a link to a copy of your file. This will make it easier to test a possible solution.

 

[netrixuser]

Unfortunately work bans me from access to either of the two sites.
I have two entries in Name Manager:

"References" cell B16 is =COUNTA(JML!A5:A26) - JML is the sheet in the post above


UserForm code:

Option Explicit

Private Sub CancelButton_Click()
Unload frmJML
End Sub

Private Sub UpdateSheet_Click()

Dim Trader As String
Dim targetRow As Integer

        If Sheets("References").Range("B17").Value = "NEW" Then        'references B17 is either NEW or EDIT - 
            If Application.WorksheetFunction.CountIf(Sheets("JML").Range("D5:D26"), Unametxt) > 0 Then
                MsgBox "User Already in the sheet", 0, "Check!"
                Exit Sub
            End If
        End If
        Trader = NTIDtxt
    
        If Sheets("References").Range("B17").Value = "EDIT" Then
            targetRow = Sheets("References").Range("B16").Value + 1    'references B16 is the countA formula mentioned above
            Else
        targetRow = Sheets("References").Range("B18").Value
        End If
    
    '''Start entering data in spreadsheet'''''

        Sheets("JML").Range("Data_Start").Offset(targetRow, 1).Value = RITM
        Sheets("JML").Range("Data_Start").Offset(targetRow, 2).Value = Unametxt
        Sheets("JML").Range("Data_Start").Offset(targetRow, 3).Value = NTIDtxt
        Sheets("JML").Range("Data_Start").Offset(targetRow, 4).Value = ActDate
        Sheets("JML").Range("Data_Start").Offset(targetRow, 5).Value = DDItxt
        Sheets("JML").Range("Data_Start").Offset(targetRow, 6).Value = Tradcpytxt
        Sheets("JML").Range("Data_Start").Offset(targetRow, 7).Value = TurretType


        If AuthYes = True Then
            Sheets("JML").Range("Data_Start").Offset(targetRow, 8).Value = "Yes"
            Else
            Sheets("JML").Range("Data_Start").Offset(targetRow, 8).Value = "No"
        End If
        If BTTYes = True Then
            Sheets("JML").Range("Data_Start").Offset(targetRow, 9).Value = "Yes"
            Else
            Sheets("JML").Range("Data_Start").Offset(targetRow, 9).Value = "No"
        End If
        If LogonYes = True Then
            Sheets("JML").Range("Data_Start").Offset(targetRow, 10).Value = "Yes"
            Else
            Sheets("JML").Range("Data_Start").Offset(targetRow, 10).Value = "No"
        End If
        If VrTestYes = True Then
            Sheets("JML").Range("Data_Start").Offset(targetRow, 11).Value = "Yes"
            Else
            Sheets("JML").Range("Data_Start").Offset(targetRow, 11).Value = "No"
        End If
        If SNOWYes = True Then
            Sheets("JML").Range("Data_Start").Offset(targetRow, 12).Value = "Yes"
            Else
            Sheets("JML").Range("Data_Start").Offset(targetRow, 12).Value = "No"
        End If
        If UpdateSSYes = True Then
            Sheets("JML").Range("Data_Start").Offset(targetRow, 13).Value = "Yes"
            Else
            Sheets("JML").Range("Data_Start").Offset(targetRow, 13).Value = "No"
        End If
        
        If UpdateFPYes = True Then
            Sheets("JML").Range("Data_Start").Offset(targetRow, 14).Value = "Yes"
                ElseIf UpDateFPNA = True Then
                Sheets("JML").Range("Data_Start").Offset(targetRow, 14).Value = "NA"
            Else
            Sheets("JML").Range("Data_Start").Offset(targetRow, 14).Value = "No"
        End If

        Sheets("JML").Range("Data_Start").Offset(targetRow, 15).Value = Comments
        Sheets("JML").Range("Data_Start").Offset(targetRow, 0).Value = DateText

        '''Stop entering data in spreadsheet'''''
        
        
        Unload Me
        MsgBox Trader & " " & "Was Entered Successfully", 0, "Update Complete"
        Exit Sub



'Call FormatCells
'Unload frmJML
End Sub

Private Sub UserForm_Initialize()
    DateText.Value = Format(Date, "dd/mm/yyyy")
End Sub


Sub JMLTracker()         'this code calls the userform to enter data "NEW"
    Sheets("References").Range("B17").Value = "NEW"
    frmJML.Show
End Sub


Sub JMLEdit()    ' this code calls the userform to edit the data "EDIT"

    Sheets("References").Range("B17").Value = "EDIT"
    frmEdit.Show
    Unload frmEdit

End Sub


'AND - the code to call the Editentry userform


Option Explicit
Private Sub CancelButton_Click()
    Unload frmEdit
End Sub
Private Sub SelectEdit_Click()
Dim targetRow As Integer
            targetRow = Application.WorksheetFunction.Match(ColE_Menu, Sheets("JML").Range("DYNUserName"), 0)
            
            Sheets("References").Range("B18").Value = targetRow

            frmJML.RITM = Sheets("JML").Range("Data_Start").Offset(targetRow, 1).Value
            frmJML.Unametxt = Sheets("JML").Range("Data_Start").Offset(targetRow, 2).Value
            frmJML.NTIDtxt = Sheets("JML").Range("Data_Start").Offset(targetRow, 3).Value
            frmJML.ActDate = Sheets("JML").Range("Data_Start").Offset(targetRow, 4).Value
            frmJML.DDItxt = Sheets("JML").Range("Data_Start").Offset(targetRow, 5).Value
            frmJML.Tradcpytxt = Sheets("JML").Range("Data_Start").Offset(targetRow, 6).Value
            frmJML.TurretType = Sheets("JML").Range("Data_Start").Offset(targetRow, 7).Value
            
'''Start  Retrieving Data From Spreadsheet'''
            If Sheets("JML").Range("Data_Start").Offset(targetRow, 8).Value = "Yes" Then
                frmJML.AuthYes = True
            Else
                frmJML.AuthYes = False
            End If
            If Sheets("JML").Range("Data_Start").Offset(targetRow, 9).Value = "Yes" Then
                frmJML.BTTYes = True
            Else
                frmJML.BTTYes = False
            End If
            If Sheets("JML").Range("Data_Start").Offset(targetRow, 10).Value = "Yes" Then
                frmJML.LogonYes = True
            Else
                frmJML.LogonYes = False
            End If
            If Sheets("JML").Range("Data_Start").Offset(targetRow, 11).Value = "Yes" Then
                frmJML.VrTestYes = True
            Else
                frmJML.VrTestYes = False
            End If
            If Sheets("JML").Range("Data_Start").Offset(targetRow, 12).Value = "Yes" Then
                frmJML.SNOWYes = True
            Else
                frmJML.SNOWYes = False
            End If
    
            If Sheets("JML").Range("Data_Start").Offset(targetRow, 13).Value = "Yes" Then
                frmJML.UpdateSSYes = True
            Else
                frmJML.UpdateSSYes = False
            End If
            If Sheets("JML").Range("Data_Start").Offset(targetRow, 14).Value = "Yes" Then
                frmJML.UpdateFPYes = True
            ElseIf Sheets("JML").Range("Data_Start").Offset(targetRow, 14).Value = "NA" Then
                frmJML.UpDateFPNA = True
            Else
                frmJML.UpdateFPYes = False
            End If
            frmJML.Comments = Sheets("JML").Range("Data_Start").Offset(targetRow, 15).Value
            frmJML.DateText = Sheets("JML").Range("Data_Start").Offset(targetRow, 0).Value
  '''Stop  Retrieving Data From Spreadsheet'''
       
            frmJML.Show
            Unload frmEdit
End Sub

Because the code uses Data_Start [=JML!$A$4] as a reference point to enter the data in the spreadsheet (well, the offset command used data_start) and also this piece of code[number/integer] to know where the next blank row is "targetRow = Sheets("References").Range("B16").Value + 1" ... if I remove a line of data from within my table of data, my code will fail because "targetRow = Sheets("References").Range("B16").Value + 1" will equal a number less than the number of entries (rows) so the data entry will overwrite an existing row of data.

if there is a better way to make my userform select the next blank row ... better in that it does not need to count the number of contiguous entries in a column, I will not need to work out a way to delete expired entries - new entries would simply be written in the next blank row - whether that be at the top, middle or bottom of the table of date

 

[mumps]

I'm sorry but I don't think that I can help without seeing your file. Perhaps you could de-sensitize the data if necessary, and upload the file from a home computer.
You can select the next blank row by using code such as the one below. You don't need a formula to refer to.

Range("A1").Copy Cells(Rows.Count, "A").End(xlUp).Offset(1)

This copies A1 and pastes it to the first available blank cell in column A.