Remove entire row

[Goofan]

Hi, I think this question has been asked before here but i couldn't find an "true answer" to the question i possess.

How do you remove an entire row?
my worksheet got 3 coloumns filled with text and with some that got nothing in them however i got 3 other coloumns that are below the others(exampel below). Now i want to remove those rows where there is nothing in them.

|19919191|1919191191|11116162
|blank|blank|blank
|19919191|1919191191|11116162
|19919191|1919191191|11116162
|blank|blank|blank
|19919191|1919191191|11116162
|19919191|1919191191|11116162
|blank|blank|blank
|19919191|1919191191|11116162
|19919191|1919191191|11116162
|19919191|1919191191|11116162
|blank|blank|blank
|19919191|1919191191|11116162
|19919191|1919191191|11116162
|blank|blank|blank|blank|blank|blank|blank|blank|blank|19919191|1919191191|11116162
|blank|blank|blank|blank|blank|blank|blank|blank|blank|19919191|1919191191|11116162
|blank|blank|blank|blank|blank|blank|blank|blank|blank|19919191|1919191191|11116162
|blank|blank|blank|blank|blank|blank|blank|blank|blank|19919191|1919191191|11116162

I hope this makes some sens atleast
And i need it as a function if that makes any diffrence...

Regards,
Thomas

 

[Goofan]

Not really. I have no idea what iRow or iCompiler are.

And do you want Sum to have a continuous record of these rows (meaning you add to existing records each time), or are you going to create a new sheet each time. And if you're doing that, what's the point?

Well iRow is "long" variable and so is iCompiler they increment in the code as the code calculate which to withdraw and such and the iRow and iCompiler stands for the row...

And the sheet normally got a spand of 100000 rows to 1 milion rows.
And the sheet will be created each time yes becouse the result will be processed by a "human" to make it go as it should (I'm doing this for work)

Having one sheet for both the result and the spill would take up alot of room wouldn't you agree? Therefor i move it over to a seperate sheet also enabling so that 2 persons can work on the diffrent sheets instead of one working with both/one huge.

Regards,
Thomas

 

[Smitty]

I think maybe you need to explain in a bit more detail what you're doing, because the way it stands now it sounds like you're only moving one row (primarily three cells - J, K & L of the last row).

I want those rows that i moveed to goto a new sheet that i named Sum and to be placed at the coloumns A, B, C and downwards. the code i got is this:

What rows are you moving? Because up to this point it was about removing blank rows.

 

[Goofan]

We moved on I found the solution on removing blanks... As said in previus post...

Now i want those that i took out (those that made the blanks) to goto a new sheet called Sum...

This should happend:

The macro first does all other stuffs calculating and such then it moves the identified rows to (new sheet made and now called Sum) I might have made it more confusing than it should be im sorry for that.

MY Macro does this: It check if value 1 + value 2 is equal to 0 if so then se if the second identifier is equal to its opponents identifier and if yes then move the 3 cells to a new sheet (This is where i'm stuck).



(I actually got a secondary problem now aswell as i moved on, Can take that later if we get this to work)


Regards,
Thomas

 

[Smitty]

Can you post your existing code?

It seems that the cart's a bit before the horse on this one.

 

[Goofan]

Sure, but how? Do i need to have a seperate link to an existing "online folder or can i upload to the site direcly? (The files)

 

[Smitty]

No, just go to the VBE, copy the code in question and paste it back here. You'll want to make sure to use code tags though:

[ code ]
' Your code here
[ / code ]

(No spaces between the brackets).

You can't upload workbooks here, although you can post a link to an online file sharing site, just be aware that very few people will look at it for a variety of reasons. I have a firewall that blocks me for instance.

 

[Goofan]

Alright thanks for the tip... here we go

Sub Koll_Ankomst()
   Dim iRow As Long, iLastRow As Long, iCompiler As Long, iValue As Long, iValue2 As Long
    Dim iReference As Long, iReference2 As Long, i As Long
    Dim bDone As Boolean, bDone2 As Boolean
    Dim sBoolean As String
    
    bDone = False
    iRow = 1
    iCompiler = 1
    
    Dim wkbSource As Workbook
    Dim wksTarget As Worksheet

    'This part doesn't work but im trying to solve it
    Set wksTarget = ActiveSheet
    Set wkbSource = Workbooks.Open("Imported.xlsx")
    wkbSource.Sheets(1).Range("A1:C1000000").Copy
    wksTarget.Range("A3").PasteSpecial Paste:=xlValue

    
    Range("b1000000").End(xlUp).Select
    iLastRow = ActiveCell.Row + 1
    i = iLastRow
    Do Until iRow = iLastRow
    
    iValue = Cells(iRow, 2).Value
    iValue2 = Cells(iCompiler, 2).Value
    
    If iCompiler = iLastRow Then
        iRow = iRow + 1
        iCompiler = 1
    End If
        
    If iValue = 0 Then
        iRow = iRow + 1
    ElseIf iValue2 = 0 Then
        iCompiler = iCompiler + 1
    Else
            
        Summering iValue, iValue2
        If iValue = 0 Then
            iReference = Cells(iRow, 3).Value
            iReference2 = Cells(iCompiler, 3).Value
            Equal iReference, iReference2
        End If
            
        If iValue = 0 And iReference2 = 0 Then
            'Cells(i, 10).Value = Cells(iRow, 2).Value
            'Cells(i, 11).Value = Cells(iCompiler, 2).Value
            'Cells(i, 12).Value = Cells(iRow, 3).Value
            Cells(iRow, 2).EntireRow.Delete
            Cells(iCompiler, 2).EntireRow.Delete
            Cells(iRow, 3).EntireRow.Delete
            Cells(iCompiler, 3).EntireRow.Delete
            Cells(iRow, 1).EntireRow.Delete
            Cells(iCompiler, 1).EntireRow.Delete
            SheetExists "Sum"
            'Sum.Cells(i, 1).Value = Cells(iRow, 2).Value
            'Sum.Cells(i, 2).Value = Cells(iCompiler, 2).Value
            'Sum.Cells(i, 3).Value = Cells(iRow, 3).Value
            iRow = iRow + 1
            i = i + 1
        End If
    End If
        
    iCompiler = iCompiler + 1
    Loop
    
End Sub

'Till för att kolla om tal 1 +- tal 2 blir 0 (*Skicka tillbaka boolean)
Function Summering(ByRef iCalc As Long, iCompiler As Long)
    Dim bAnswer As Boolean
    
    If iCalc + iCompiler = 0 Then
        iCalc = iCalc + iCompiler
    End If
    
End Function

'Till för att se om de två talen är likadana (*Skicka tillbaka boolean)
Function Equal(ByRef x As Long, y As Long)
    Dim bAnswer As Boolean
    
    If x = y Then
    y = 0
    End If
    
End Function

Private Function SheetExists(SheetName As String)
    ' Retunerar TRUE om sheetet finns i den aktiva workboken
    Dim x As Worksheet
        On Error Resume Next
            Set x = ActiveWorkbook.Sheets(SheetName)
                If Err = 0 Then
                    'MsgBox = "Du har redan en Sheet som heter Sum öppen. Var god stäng den!"
                Else
                    Sheets.Add.Name = SheetName
                End If
End Function

 

[Smitty]

For the part that doesn't work, you need to activate the second workbook before you can paste into it.

 

[Goofan]

well how do you activate it then? couse I have been sitting on this for a while...

Learning alot thank you...

the other part i still need to figure out is how to pass the information over to the sum worksheet...

This part:

If iValue = 0 And iReference2 = 0 Then            
'Cells(i, 10).Value = Cells(iRow, 2).Value             
'Cells(i, 11).Value = Cells(iCompiler, 2).Value             
'Cells(i, 12).Value = Cells(iRow, 3).Value             
Cells(iRow, 2).EntireRow.Delete             
Cells(iCompiler, 2).EntireRow.Delete             
Cells(iRow, 3).EntireRow.Delete             
Cells(iCompiler, 3).EntireRow.Delete             
Cells(iRow, 1).EntireRow.Delete             
Cells(iCompiler, 1).EntireRow.Delete             
SheetExists "Sum" 
-------------            
            
'Sum.Cells(i, 1).Value = Cells(iRow, 2).Value             
'Sum.Cells(i, 2).Value = Cells(iCompiler, 2).Value             
'Sum.Cells(i, 3).Value = Cells(iRow, 3).Value
 -------------            

iRow = iRow + 1             
i = i + 1         
End If

 

[Smitty]

well how do you activate it then? couse I have been sitting on this for a while...

Set a reference to the ActiveWorkbook, then you can use something like this:

wbsTarget.Activate