Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

Short question: does anyone have a good resource for learning how to put if then statements into macros, specifically looking for certain strings in a cell and deleting that row?

I am building a vba macro to reformat a .csv dump from our internal project tracking system into a more user friendly format. Its all very simple formatting stuff until this part. I have zero VBA experience and no idea how to get logic in there.

I looked for tutorials online but they all seem too high level for what I'm trying to do.

# ¿ Feb 22, 2012 22:56

Adbot: ADBOT LOVES YOU

# ¿ Apr 24, 2024 11:47

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

sweet thanks, that looks like it will do the trick.

It iterates through the whole column starting at A, right? So I just need (whatever column)1 and it will step through until it hits a blank cell?

Second newbie question, can I just do the same for loop with a different column and string but not change variables for other fields?

Xguard86 fucked around with this message at 19:42 on Feb 23, 2012

# ¿ Feb 23, 2012 18:37

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

Here's what I've got, three loops on the same column but different strings. Is there any low hanging fruit style things I can do to optimize this? Its only 1200 or so rows so it can run like a dog and not matter, but I like doing things right.

code:

Sub delete_row()
   Dim i As Long
   Dim last As Long
   
   last = ActiveSheet.UsedRange.Rows.Count - 1

   For i = last To 1 Step -1
      If InStr(ActiveSheet.Range("F1").Offset(i, 0).Value, "CarrierSpecs") > 0 Then
         ActiveSheet.Range("A1").Offset(i, 0).EntireRow.Delete
      End If
   Next i
   last = ActiveSheet.UsedRange.Rows.Count - 1

   For i = last To 1 Step -1
      If InStr(ActiveSheet.Range("F1").Offset(i, 0).Value, "EACopy") > 0 Then
         ActiveSheet.Range("A1").Offset(i, 0).EntireRow.Delete
      End If
   Next i
   For i = last To 1 Step -1
      If InStr(ActiveSheet.Range("F1").Offset(i, 0).Value, "Export") > 0 Then
         ActiveSheet.Range("A1").Offset(i, 0).EntireRow.Delete
      End If
   Next i
End Sub

# ¿ Feb 24, 2012 17:56

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

Excellent, I knew I could close that up.

...and another stumbling block.

My most recent code, I've tried many different versions:

code:

Worksheets("Macro_test").Range("A2").Sort _
        Key1:=Worksheets("Macro_test").Range("G2"), _
        Key2:=Worksheets("Macro_test").Range("H2")
        Header = Range("A1:X1")

I want to sort everything on columns G and H but I have headers in row 1. I can't get it to stop including those rows in the sort. This should be simple, I don't know what I'm putting in wrong. Can you use a specific range for Header or can it only be that xlYes No Guess stuff?

Xguard86 fucked around with this message at 18:36 on Feb 24, 2012

# ¿ Feb 24, 2012 18:33

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

Alright almost got this done, you've been a great help Old James.

One more question: I want to insert a new row and then number it sequentially(so row 1 has cell A1 with a '1' row two's A2 has a '2' etc.)

I can do the insert and I've figured out how to get it to count sequentially but I only want it to assign a number to rows with occupied cells, not just count down until it hits the page limit.

I'm guessing some kind of 'If' statement will do it, but I don't know how to write it where it will check for anything at all in the adjacent cell.

# ¿ Feb 24, 2012 22:36

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

running that returns:

quote:

Unable to get the large property of the WorksheetFunction class

I'm not sure why, I've been googling around but I don't see anything to explain why it would error out. Maybe something to do with my insert for column A?

code:

Sub rowvalueinsert()
     Dim i As Long
     Dim last As Long
     
     Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
     For i = 0 To last
          If Application.WorksheetFunction.CountA(Range("1:1").Offset(i, 0)) > 0 Then
               If IsError(Application.WorksheetFunction.Large(Columns("A:A"), 1)) Then
                    Range("A1").Offset(i, 0).Value = 1
               Else
                    Range("A1").Offset(i, 0).Value = _
                    Application.WorksheetFunction.Large(Columns("A:A"), 1) + 1
               End If
          End If
     Next i
End Sub

# ¿ Feb 25, 2012 01:19

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

Old James posted:

It looks like the error is occurring when it tries to find the largest existing value in column A while column A is blank. Which is odd, because I told it if the large function returned an error to make the value 1. The version above would have picked up numbering after any existing value in column A. But the version below is simpler and just starts at 1.
code:
Sub rowvalueinsert()
     Dim i As Long
     Dim last As Long
     Dim counter as long
     
     Columns("A:A").Select
     Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
     counter = 1
    
     For i = 0 To last
          If Application.WorksheetFunction.CountA(Range("1:1").Offset(i, 0)) > 0 Then
               Range("A1").Offset(i, 0).Value = counter
               counter = counter + 1
          End If
     Next i
End Sub

I know its been like 3 weeks but thanks, I actually ended up with pretty much that after playing with it over the weekend. VBA seems so simple and then things just... don't work. Very frustrating.

# ¿ Mar 14, 2012 23:11

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

what is wrong with this vba? I put a comment next to what it is flagging as an error. It says "object required". I'm sure its simple but I am bad at this. Can I not do " " for blank cell? Do I need something else?

code:

Sub delete_outside_offices()

Dim rng1 As Range
Dim rng2 As Range
Dim i As Integer


Set rng1 = Workbooks("MONTH_END_MACRO.xlsm").Sheets("usr").Range("C1")
Set rng2 = Workbooks("MONTH_END_MACRO.xlsm").Sheets("qa").Range("C1")
i = 0

Do
    If rng1.Value <> "Dallas Office" Or rng1.Value <> "Other" Then
    rng1.EntireRow.Delete
    i = i + 1
    End If

##highlights this as the error
Loop Until rng1.Offset(i, 0).Value = ""


Do
    If rng2.Value <> "Dallas Office" Or rng2.Value <> "Other" Then
    rng2.EntireRow.Delete
    i = i + 1
    End If

Loop Until rng2.Offset(i, 0).Value = ""



End Sub

# ¿ Mar 19, 2012 16:09

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

ah I see. I never even thought about how deleting rows would screw up the loop.

# ¿ Mar 19, 2012 17:10

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

This is a simple question but I cannot phrase it well enough to get decent google returns.

I have two columns:

column A contains 30 entries
column B contains 4

I'd like to concatenate each entry in column A with all four entries from column B then move to the next entry in column A and repeat. Formulas or VBA are fine or any other solution thats outside the box.

Start:
A 1
B 2
C 3

End:
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3

This is a one time operation and I'm C/P the data into something else so any solution that gets values works. This kind of scenario (different sized arrays) does come up quite a bit for me so I'd love if someone could give me a general method when this appears. I can see the idea in a psuedo-code kind of way but can't get the details.

# ¿ Mar 20, 2013 18:22

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

That works perfectly and now I know that is a Cross Join so I can work with it in the future.

I think you answered all my questions in this thread like a year ago Old James. Your a goddamn national treasure.

# ¿ Mar 20, 2013 19:57

Adbot: ADBOT LOVES YOU

# ¿ Apr 24, 2024 11:47

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

Busy Bee posted:

You might not have time for this but one thing that I've found very helpful in the past is starting a new workbook with the raw data and rebuilding everything to understand how it fits together. Its a great learning tool and can really improve those "legacy XLs" that have been passed around for years and get kind of bloated (if that is what this is).

# ¿ Apr 25, 2013 16:12

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread