Extra Commas in Exported CSV

[tomgrandy]

I have several Excel spreadsheets with macros that run on up to 70,000 items. I wrote the Macros to allow for up to 70,000 rows, but some of the spreadsheets only have 450 rows.

As a result, in the export to CSV, I end up with a series of commas up to the 70,000 line which messes up when I try to import the CSV as a feed into my Drupal site.

This is a newbie question, but how can I write these in macros to just run to the last line that has values in it and not how I did to account for the largest spreadsheet?

For example, I have written some as such:

Range("e3:e70000").NumberFormat = "mm-dd-yy"••••

or

For Each cell In Range("c2:c70000")

or

Range("J3:J70000").Replace What:=".jpg", Replacement:="_2.jpg"

I realize it is the 70000 that is getting me into trouble, but am unsure how to write it to read the Range from the first line J3 to the last row with data (J not fixed at 70,000)

Thanks!

 

[rpaulson]

lr=Cells(Rows.Count, "J").End(xlUp).Row ' this will find the last row in column J

For Each cell In Range("c2:c" & lr)....

 

[tomgrandy]

Thanks @rpaulson ! Much appreciated.

 

[tomgrandy]

@rpaulson - When I use the sample above, I get an error of:

For Each cell In Range("c2:c" & lr)
If InStr(cell.Value, "Alabama") > 0 Then
cell.Offset(0, 3).Value = "Alabama"
End If
If InStr(cell.Value, "Alaska") > 0 Then
cell.Offset(0, 3).Value = "Alaska"
End If

 

[rpaulson]

i don't see there you ever set the value for lr

you need to define lr, see below

dim lr as long
lr=Cells(Rows.Count, "C").End(xlUp).Row ' this will find the last row in column
For Each cell In Range("c2:c" & lr)
...

 

[tomgrandy]

Ah, that would make the world of difference.

I'm new at this and trying to learn. People like you are super helpful!!

 

[tomgrandy]

What am I missing here? Getting a compile error. Object required. :

Sub ChangeDateFormat()

    Application.ScreenUpdating = False
    
    Dim lr As Long
        lr = Cells(Rows.Count, "e").End(xlUp).Row ' this will find the last row in column
    For Each cell In Range("e2:e" & lr)
    Set NumberFormat = "yy-mm-dd"
    
    Application.ScreenUpdating = True
    
    MsgBox "Change Date Format Macro Complete!"

End Sub

 

[rpaulson]

For Each cell In Range("E2:E" & lr)
    cell.NumberFormat = "yy-mm-dd"
Next cell

 

[tomgrandy]

@rpaulson - Thank you!