Help with modifying macro to check if cell in range is empty

[matija385]

hi all, i would appriciate some help

i have two sheets named Unos_treninga and Test. I need to copy range I16:AA40 from Unos_treninga sheet to sheet Test to first empty row. There is condition - copy cells if cell in column I is not empty.

data is entered in every row in range, there is no possibility to have for example I16 populated, I17 empty, and I18 again not empty. So, macro should check based on cells in I column if is empty, stop copying, if not, copy cell in that row within the range.

here is macro i use for copying, but it doesn't check if cell in I is not empty, but coppies whole range.

Sub Copy_Paste()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet


Set copySheet = Worksheets("unos_treninga")
Set pasteSheet = Worksheets("test")


copySheet.Range("I16:AA40").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Thanks for help,

Kind regards,
Matija

 

[My Aswer Is This]

Try this:

Sub Test()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Sheets("Unos_treninga").Select
Lastrow = Sheets("Test").Cells(Rows.Count, "A").End(xlUp).Row
    For i = 16 To 40
        If Cells(i, 9).Value <> "" Then
            Sheets("Test").Rows(Lastrow).Value = Rows(i).Value
            Lastrow = Lastrow + 1
        Else
            Application.ScreenUpdating = True
            Exit Sub
        End If
    Next
Application.ScreenUpdating = True
End Sub

 

[matija385]

Try this:

Sub Test()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Sheets("Unos_treninga").Select
Lastrow = Sheets("Test").Cells(Rows.Count, "A").End(xlUp).Row
    For i = 16 To 40
        If Cells(i, 9).Value <> "" Then
            Sheets("Test").Rows(Lastrow).Value = Rows(i).Value
            Lastrow = Lastrow + 1
        Else
            Application.ScreenUpdating = True
            Exit Sub
        End If
    Next
Application.ScreenUpdating = True
End Sub

Thanks for quick reply. It's not working for me. When i run macro, it copies range with not empty I column (16-40 rows), but its not copying it to first empty row in sheet Test, nor it starts from column A which it should, sorry, i forgot to mention it before.

 

[matija385]

Thanks for quick reply. It's not working for me. When i run macro, it copies range with not empty I column (16-40 rows), but its not copying it to first empty row in sheet Test, nor it starts from column A which it should, sorry, i forgot to mention it before.

Also, i don't need macro to copy entire row, but only range I16 - AA40 if cell in I column is not empty. If, for ie cell i19 is empty, then it should copy only range I16 - AA18, not the entire row.

Thnx,
Matija

 

[My Aswer Is This]

I do not understand. I tested this and it worked. Your quote " copy cells if cell in column I is not empty.
"

Thanks for quick reply. It's not working for me. When i run macro, it copies range with not empty I column (16-40 rows), but its not copying it to first empty row in sheet Test, nor it starts from column A which it should, sorry, i forgot to mention it before.

 

[My Aswer Is This]

I'm confused.
If cell I is empty you want it copied over or you do not want it copied??
Your quote:"If, for ie cell i19 is empty, then it should copy only range I16 - AA18"
Your quote: "There is condition - copy cells if cell in column I is not empty."

 

[My Aswer Is This]

This was never mentioned in your original post:
Your Quote:
"it should copy only range I16 - AA18"

 

[matija385]

This was never mentioned in your original post:
Your Quote:
"it should copy only range I16 - AA18"

So, in this file i have on sheet Unos_treninga range from I16 - AA40... i enter some data in it... what i need is when i click button called Spremi promjene to copy that range on sheet Polaznici.

But, copycells from I16 - AA16 if I16 i not empty, copy I17:AA17 if I17 is not empty, copy I18:AA18 if I18 is not empty...

And need that to copy on sheet Polaznici (i changed name of sheet test to Polaznici) starting from row 2, column A, and copy it to first empty row.

Hope this helps you to help me it would be great if i could post you my file...

Thnx