Help with VBA, hiding/deleting rows

jmerch

New member
Joined
Feb 14, 2012
Messages
32
Reaction score
0
Points
0
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

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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

New member
Joined
Feb 14, 2012
Messages
32
Reaction score
0
Points
0
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

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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.
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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

Code:
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
 
Last edited by a moderator:

jmerch

New member
Joined
Feb 14, 2012
Messages
32
Reaction score
0
Points
0
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

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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

New member
Joined
Feb 14, 2012
Messages
32
Reaction score
0
Points
0
Ahh, I get it. Never thought of looking at it that way. Thanks again for your help, Simi!
 
Top