Help with VBA, hiding/deleting rows

[jmerch]

Hello, new to the forum. I'm somewhat familiar with VBA, just enough to get me by.

I have created a script that deletes certain rows/columns in an Excel Template my company has, then it looks at a certain column and if there is no value, it deletes the row. So essentially it condenses a large template into just the information our accountant needs to extract. The script works fine, except now we need the flexibility for a user to add/delete rows as they wish. I'm just not sure how to incorporate that. Here's a longer explanation of why.

My initial script deletes entire columns from J to the end. Then it deletes entire rows from like 1550 all the way down. The reason for this is because for our accountant to import our data to their program, these have to be deleted. If they're not, the program reads a comma (,) in place of these fields. This doesn't affect anything except then they get an error report when importing. This is what we're trying to avoid. So, the user of the excel sheet is going to be able to add/delete rows as needed which then messes up my set rows to delete.

Any suggestions?

[Simi]

Could you upload a sample of the data you have and what you are trying to accomplish?
I am not understanding how a blank cell is importing a (,). Unless you are converting the data from the excel sheet to a csv file, and it looks like:
A1,B1,C1,D1
A2,B2,,D2
,,,
,,,
,,,
If this is the case, you will need to put a check to not export the data if cell A(row#) is empty.

[jmerch]

Here is file. I should also mention the end of my code saves the file as a .csv and closes at the end. Where the "NAME" is, is where names would go all the way down the list. This group is where people would need to insert rows or delete rows. The button to run the script is at the top on the right. The users fill out the name and some other data, and the script reads the UNITS field and condenses everything based on this. The orange/colored fields are what gets deleted per the script.

I get what you're saying with the (,) but instead of a check for comma's and not export, I'd rather it ignored commas when it exports....if that's possible.

[Simi]

jmerch, I do not see the attachment. If you click on reply then the go advanced button you will be able to attach the file.

[jmerch]

Sorry, my bad payroll CERT MASTER-upload test.xlsm

[Simi]

I took out your hard coded row data where the Totals are located, also I got rid of the (,)'s and save it as a tab delimited filed.
I added the line 'modified by simi at the begining and end of the sections I modified so you can easily see what changed.
I hope this helps you.

Simi

Private Sub CommandButton1_Click()'modified by simi
'declare variables
Dim iRowCounter As Integer
iRowCounter = 1
Do While Trim(UCase(ActiveSheet.Range("C" & iRowCounter))) <> "TOTALS"
   iRowCounter = iRowCounter + 1
Loop
'modified by simi


ActiveWorkbook.Save
ActiveSheet.Unprotect
'modified by simi
Range(iRowCounter & ":1048576").Select
'modified by simi
Selection.EntireRow.Delete
Range("1:2").Select
Selection.EntireRow.Delete
Range("L:XFD").Select
Selection.EntireColumn.Delete
Range("$A$1").Select
'modified by simi
    For Each cell In Range("J1:J" & (iRowCounter - 1))
'modified by simi
        If cell.Value < "1" Then cell.EntireRow.Hidden = True
        Next cell
        
    Dim r As Range, k As Range
    With ActiveSheet
        Set r = .Range("A1:A" & .Cells.SpecialCells(xlCellTypeLastCell).Row)
        Set k = r.SpecialCells(xlCellTypeVisible)
        r.EntireRow.Hidden = False
        k.EntireRow.Hidden = True
        r.SpecialCells(xlCellTypeVisible).EntireRow.Delete
        r.EntireRow.Hidden = False
    End With
Range("C:C").Select
Selection.EntireColumn.Delete
Range("$A$1").Select
Range("F:G").Select
Selection.EntireColumn.Hidden = False
ActiveSheet.Protect
'modified by simi
ActiveWorkbook.SaveAs ActiveWorkbook.FullName & ".txt", FileFormat:=xlText
'modified by simi
ActiveWorkbook.Close True
End Sub

[jmerch]

As far as i can tell, that works! I will have the necessary people test it. I'm trying to figure out what it's doing to accept the addition or deletion of rows. Where I was strictly deleting specific rows, what is this looking for?

Thanks so much for the help!

[Simi]

This is still functioning the exact same way you had it. The only difference is it counts down from the top until it finds the word "TOTALS", then uses that row as the variable

iRowCounter. that way if your "Totals" is not on row 1576 (or whatever row it was) it still works, thus giving you the capability of adding or deleting rows and having the code still function.

[jmerch]

Ahh, I get it. Never thought of looking at it that way. Thanks again for your help, Simi!