Add two rows of text in the end of my documents

[smxviito]

Hi!
I have a problem and I would like some help.

I have about 500 excel files that I need to add two rows of text to after
the last row.

Most of the files have different sizes, some are 42 rows, while others are 56 or 72 etc.

I don't have a lot of experience with Excel or VBA so I need some assistance.

I'm using Excel 2007.

This would really save me a lot of time!!

Thanks :)

[p45cal]

Are all the files in the same folder?
Are there any files in the same folder that you don't want processing?
Is there only one sheet per workbook? If not do you (a) want blank sheets to be processed, (b) only the first sheet regadless of whether there are other sheets containing data in the same workbook or (c) something else?

[smxviito]

All the files are in the same folder, I think there's only one sheet in most of the documents, but if there're more than one sheet I only want the text to be added to the first sheet, (the sheet that shows when you open the document.)

I want all the files to be processed :)

Thanks! :)

[p45cal]

Is there one column (in ALL the sheets/workbooks) which will ALWAYS have something in the last row of the sheet (so we can identify which is the last row)?

(This is not a must-have - there are other ways to id the last row)

[smxviito]

No, i don't think so. It's usually different columns, anywhere from A to AM.
Sometimes the last two rows are merged, sometimes not.

[p45cal]

If the sheet is empty do you still want to add two lines of text?

[smxviito]

No, I only want the code to work in the active sheet, the one that shows when I open the workbook :)

[p45cal]

…and if that one is empty?

[smxviito]

They aren't empty. They all have data in them :)

[p45cal]

working on it

[smxviito]

thanks man :)

[GTO]

All the files are in the same folder, I think there's only one sheet in most of the documents, but if there're more than one sheet I only want the text to be added to the first sheet, (the sheet that shows when you open the document.) ...


No, I only want the code to work in the active sheet, the one that shows when I open the workbook :)

Hi there,

Simply for clarification as you are already getting great help from Pascal:

Which sheet is to be updated, Sheet 1, or the sheet that is active when you open the workbook?

[p45cal]

Run the macro blah from a workbook which is not saved in the folder you want to process. You should make one-off adjustments where indicated in 3 places in the code. It handles merged cells at the bottom, filtered ranges should there be any, hidden rows, empty but formatted cells, not sure about lists/tables.
Sub blah()
mypath = "C:\a folder\another folder\testt\" 'adjust this and include a final backslash
myFile = Dir(mypath & "*.xls*")
Do While myFile <> ""
Set mybook = Workbooks.Open(mypath & myFile)
Set sht = mybook.ActiveSheet
Set Destn = sht.Cells(GetLastRowWithData(sht), 1).Offset(1)
Destn.Value = "Hi there, 1st added row of text" 'adjust
Destn.Offset(1).Value = "Hi there, 2nd added row of text" 'adjust
mybook.Close True
myFile = Dir()
Loop
End Sub


Public Function GetLastRowWithData(TheSheet) As Long
'mostly not not my code
If WorksheetFunction.CountA(TheSheet.Cells) > 0 Then
Dim ExcelLastCell As Object, lRow As Long, lLastDataRow As Long, l As Long
Set ExcelLastCell = TheSheet.Cells.SpecialCells(xlLastCell)
lLastDataRow = ExcelLastCell.Row
lRow = ExcelLastCell.Row
Do While Application.CountA(TheSheet.Rows(lRow)) = 0 And lRow <> 1
lRow = lRow - 1
Loop
lLastDataRow = lRow
GetLastRowWithData = lLastDataRow
Else
GetLastRowWithData = 0
End If
End Function

[smxviito]

Thanks for all the help!

It worked, but not perfectly.

I noticed that in some of my documents the last two rows where merged without text in the cells, but with text boxes in front of the megred cells.

I also noticed that some of the documents had column A empty, is it possible to skip a column if it is empty?


The macro worked for all my documents, except for the ones with the last two rows merged without text in them. It also failed to find the last cell when there were data in row 35 and that row was merged with row 36. In that case it put my first row in row 36.

[p45cal]

I also noticed that some of the documents had column A empty, is it possible to skip a column if it is empty?What do you mean by 'skip' a column? Not add the two rows of text at all? Add them to column B instead?
The macro worked for all my documents, except for the ones with the last two rows merged without text in them. It also failed to find the last cell when there were data in row 35 and that row was merged with row 36. In that case it put my first row in row 36.I can probably handle this

[p45cal]

Not forgetting also to answer my queriy in the last message, try this tweak to handle empty merged rows:Sub blah()
mypath = "C:\Users\Pascal\testt\" 'adjust this and include a final backslash
myFile = Dir(mypath & "*.xls*")
Do While myFile <> ""
Set mybook = Workbooks.Open(mypath & myFile)
Set sht = mybook.ActiveSheet
Set destn = sht.Cells(GetLastRowWithData(sht), 1).Offset(1)
Do While destn.MergeCells
Set destn = destn.Offset(1)
Loop
destn.Value = "Hi there, 1st added row of text" 'adjust
destn.Offset(1).Value = "Hi there, 2nd added row of text" 'adjust
mybook.Close True
myFile = Dir()
Loop
End Sub