[VBA] Script is ignoring worksheets with blank cells

Hi all,

I have some issue with the following code :

My worksheets tend to have some blank cells in column A and B, so the current script only pulls data where there are non-empty cells in column A, is there a workaround here as I would want to include the rows that has empty cells in column A as well in my "Combined" Sheet. Please refer to attached for a picture reference. Thanks for reading!

Quote from royUK

You can't use CurrentRegion if the data has empty Rows and/or empty columns. Try this

Code

1. Option Explicit
2. Sub x()
3. Dim oWs As Worksheet
4. Dim iX As Integer
5. For Each oWs In ThisWorkbook.Worksheets
6. With oWs
7. Select Case .Name
8. Case "Script", "Combined"
9. 'Do nothing
10. Case Else
11. iX = iX + 1
12. ''/// only copy headers first time
13. If iX = 1 Then
14. .Range(.Cells(7, 1), .Cells(.Rows.Count, 2).End(xlUp)).Copy Sheets("Combined").Range("A1")
15. Else
16. .Range(.Cells(8, 1), .Cells(.Rows.Count, 2).End(xlUp)).Copy Sheets("Combined").Cells(.Rows.Count, 1).End(xlUp).Offset(1)
17. End If
18. End Select
19. End With
20. Next oWs
21. End Sub

Display More

Hi Roy, thank you very much for your assistance, I tried inputting your code, however it is running into error :

Run Time error 9 "Subscript out of range"

Any idea what would be causing this?

My full code if it helps

Quote from CedricYong

Hi Roy, thank you very much for your assistance, I tried inputting your code, however it is running into error :

Run Time error 9 "Subscript out of range"

Any idea what would be causing this?

My full code if it helps

Code

1. Sub Combine()
3. Dim oWs As Worksheet, TargetWS
4. Dim rRng As Range
5. Dim iX As Integer
6. Dim ws As Worksheet
8. For Each oWs In ThisWorkbook.Worksheets
9. With oWs
10. Select Case .Name
11. Case "Script", "Combined"
12. 'Do nothing
13. Case Else
14. iX = iX + 1
15. ''/// copy headers first time
16. If iX = 1 Then
17. .Range(.Cells(7, 1), .Cells(.Rows.Count, 2)).Copy Sheets("Combined").Range("A1")
18. Else
19. .Range(.Cells(8, 1), .Cells(.Rows.Count, 2)).Copy Sheets("Combined").Cells(.Rows.Count, 1).End(xlUp).Offset(1)
20. End If
21. End Select
22. End With
23. Next oWs
25. Set ws = ActiveSheet
26. With ActiveSheet
27. Worksheets(1).Select
28. Dim last_row As Long
29. last_row = .Cells(.Rows.Count, "F").End(xlUp).Row - 1
30. MsgBox (last_row), Title:="Number of transactions"
31. End With
33. With ActiveSheet
34. Worksheets(1).Select
35. Dim sht As Worksheet
36. ThisWorkbook.Worksheets("Combined").Cells.EntireColumn.AutoFit
37. End With
38. End Sub

Display More

Display More

scratch this qn, as I forgot to add the worksheet "Combined" hence the error.

Quote from royUK

You can't use CurrentRegion if the data has empty Rows and/or empty columns. Try this

Code

1. Option Explicit
2. Sub x()
3. Dim oWs As Worksheet
4. Dim iX As Integer
5. For Each oWs In ThisWorkbook.Worksheets
6. With oWs
7. Select Case .Name
8. Case "Script", "Combined"
9. 'Do nothing
10. Case Else
11. iX = iX + 1
12. ''/// only copy headers first time
13. If iX = 1 Then
14. .Range(.Cells(7, 1), .Cells(.Rows.Count, 2).End(xlUp)).Copy Sheets("Combined").Range("A1")
15. Else
16. .Range(.Cells(8, 1), .Cells(.Rows.Count, 2).End(xlUp)).Copy Sheets("Combined").Cells(.Rows.Count, 1).End(xlUp).Offset(1)
17. End If
18. End Select
19. End With
20. Next oWs
21. End Sub

Display More

However Roy, even with your amendments, the script fails to combine all the data onto one. I've attached a file for benchmarking, hope you find it useful as I cannot resolve the issue, and I do appreciate your help greatly!

If everything is working, there should be 326 rows of data.

Thanks in advance!

Quote from royUK

Try this. Column E seems to have entries for the whole database so I've used that

Ty roy, that worked perfectly for my needs!