Looping help to skip blank rows vba

[Vlr516]

I am moving data from a source sheet to my activesheet. When I do, any blank rows on my source sheet are copying over as well. I do not want this. I want on my activesheet for all the data to be compacted together. How do I do this--what am I missing in my code below?

Sub SkipBlankRows
Dim finalrow as long

finalrow = sourcesheet.cells(rows.count, 3).End(xlup).row

for i=6 to finalrow

If sourcesheet.cells(i,3)<> "" Then
Activesheet.Cells(i,3).value = sourcesheet.cells(I,3).value​

End if​

Next I
End Sub

 

[Domenic]

Try...

[FONT=Courier New][COLOR=darkblue]Sub[/COLOR] SkipBlankRows()
    [COLOR=darkblue]Dim[/COLOR] sourcesheet [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] finalrow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] j [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] sourcesheet = Worksheets("Sheet2") [COLOR=green]'change the sheet name accordingly[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] sourcesheet
        finalrow = .Cells(.Rows.Count, 3).End(xlUp).Row
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    j = 2 [COLOR=green]'starting row for the active sheet[/COLOR]
    [COLOR=darkblue]For[/COLOR] i = 6 [COLOR=darkblue]To[/COLOR] finalrow
        [COLOR=darkblue]If[/COLOR] sourcesheet.Cells(i, 3) <> "" [COLOR=darkblue]Then[/COLOR]
            ActiveSheet.Cells(j, 3).Value = sourcesheet.Cells(i, 3).Value
            j = j + 1
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
End [COLOR=darkblue]Sub[/COLOR][/FONT]

Hope this helps!

 

[JackDanIce]

Try:

Sub SkipBlankRows()

    Dim x   As Long
    
    x = Sourcesheet.Cells(Rows.count, 3).End(xlUp).row - 5
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        With .Cells(6, 3).Resize(x)
            .Value = Sourcesheet.Cells(6, 3).Resize(x).Value
            .SpecialCells(xlCellTypeBlanks).Delete xlUp
        End With
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[My Aswer Is This]

There are lots of ways to do this.
But since I believe your trying to learn I wrote this script.
I believe you want the entire row copied over an not just one column

Sub SkipBlankRows()
Dim finalrow As Long
Dim x As Long
x = 1
finalrow = Sheets("sourcesheet").Cells(Rows.Count, 3).End(xlUp).Row
    For I = 6 To finalrow
        If Sheets("sourcesheet").Cells(I, 3) <> "" Then
            Rows(x).Value = Sheets("sourcesheet").Rows(I).Value
            x = x + 1
        End If
    Next I
End Sub

 

[Rick Rothstein]

Assuming your source data are constants (that is, not formulas) and that the destination sheet is the active sheet, here is another macro for you to consider...

[table="width: 500"]
[tr]
	[td]Sub MoveSourceData()
  Dim StartCell As String, Source As Worksheet
  Set Source = Sheets("Sheet1")
  StartCell = "C2"
  With Source.Range("C6", Source.Cells(Rows.Count, "C").End(xlUp)).SpecialCells(xlConstants)
    .Copy Range(StartCell)
    Range(StartCell).Resize(.Count).ClearFormats
  End With
End Sub[/td]
[/tr]
[/table]

 

[Vlr516]

You all are wonderful. I was able to get it to work. Thank you so much!!!

I always see Dim I as Interger, J as Interger. Why are they Long here? What is the difference?

I have two last questions:
1) If I wanted to save my active sheet into its own workbook using part of the sourcesheet name, how would I do that?
2) When I add a keyboard shortcut, my macro jams up. Why would this be?

 

[Joe4]

Integers cap out at 32767. The number of rows on an Excel sheet can be over 1 million rows. The Long data type goes over 2 million.
So an Integer value may be not be big enough, depending on the number of rows with data you have.
If you have over 32767 rows, you would get errors in your VBA code with the Integer data type.
So using Long is safer, and eliminates the possibility of that error.

See here: http://www.informit.com/articles/article.aspx?p=339929&seqNum=2

 

[Rick Rothstein]

The Long data type goes over 2 million

Typo Alert... that should be billion, not million (-2,147,483,648 to 2,147,483,647 to be exact).

 

[JackDanIce]

Also, 32-bit systems convert Integer types into Long types, so marginally quicker to declare as Long from the onset:
https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long

 

[Joe4]

[COLOR=#333333]Typo Alert... that should be [/COLOR][B]billion, not million (-2,147,483,648 to 2,147,483,647 to be exact).[/B]

Hey, I was only off by a factor of 10^3! Its the end of a long week...

In any event, it is more than enough to account for any number of rows one may have.