Results 1 to 9 of 9

Thread: Help with VBA, hiding/deleting rows

  1. #1

    Help with VBA, hiding/deleting rows



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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.

  3. #3
    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.

  4. #4
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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.

  5. #5

  6. #6
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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 Bob Phillips; 2012-02-15 at 01:56 PM. Reason: Added code tags

  7. #7
    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!

  8. #8
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •