Excel Macro - Fetching the values of one column based on the values from other column

Question

I need a macro to write the row values present in column A if there is a value present in column B .

For example :

Column A   Column B
Arjun
Arun         12

For the above example, I need a macro which can write "Arun 12" in Sheet2 of the work book with the Headers "Name" and "Hours".Before this the macro should clear the data present in Sheet two completely.

What have you tried so far? Share your code attempt and we gladly help you — Shai Rado, Apr 24 '17 at 11:18

score 1 · Answer 1 · edited Sep 21 '17 at 16:13

This will copy the all rows of columns A and B from Sheet1 to Sheet2 if B is not a Null string. And also will add the headers "Name" and "Hours".

Option Explicit 'requires that every variable has to be defined before use, e.g. with a Dim statement. 

Sub DoStuff_GoodPractice()
    Dim lastRowSrc As Long, lastRowDest As Long, i As Long 'declare row counts as Long so all rows can be used
    Dim shtSource As Worksheet, shtDestination As Worksheet

    Set shtSource = ThisWorkbook.Worksheets("Sheet1")  'full qualified identification of the worksheets
    Set shtDestination = ThisWorkbook.Sheets("Sheet2")

    lastRowSrc = shtSource.Range("A" & shtSource.Rows.Count).End(xlUp).Row 'determine the last used row

    'clear destination sheet and write headers:
    shtDestination.Cells.Clear
    shtDestination.Range("A1").Value = "Name"
    shtDestination.Range("B1").Value = "Hours"

    lastRowDest = 1   'start with row 1 as destination

    For i = 1 To lastRowSrc 'loop through all used rows
        If shtSource.Range("A" & i).Value <> vbNullString And _
           shtSource.Range("B" & i).Value <> vbNullString Then 'check if cells are not a null string
            shtSource.Range("A" & i & ":B" & i).Copy Destination:=shtDestination.Range("A" & lastRowDest + 1) 'copy current row
            lastRowDest = lastRowDest + 1 'jump to the last used row in destination
        End If
    Next i
End Sub

score 0 · Answer 2 · answered Apr 24 '17 at 10:58

0

This should accomplish what you're after.

Sub DoStuff()

Dim lastRow As integer, lastRowSheet2 As integer, i As Integer
Dim sheet1 As WorkSheet, sheet2 As Worksheet

Set sheet1 = Sheets("Sheet1")
Set sheet2 = Sheets("Sheet2")

lastRow = sheet1.Range("A" & Rows.Count).End(xlUp).Row

sheet2.Cells.Clear

For i = 1 To lastRow

If sheet1.Range("A" & i).Value <> "" And sheet1.Range("B" & i).Value <> "" then

    lastRowSheet2 = sheet2.Range("A" & Rows.Count).End(xlUp).Row

    sheet1.Range("A" & i & ":B" & i).Copy Destination:= sheet2.Range("A" & lastRowSheet2 + 1)

End If

Next i

End Sub

answered Apr 24 '17 at 10:58

Skaterhaz

300
2
12

`lastRow`, `lastRowSheet2` and `i` should be of the type `Long`. Excel has more rows than `Integer` can handle. – Pᴇʜ Apr 24 '17 at 11:00
For safety and completeness, perhaps yes – Skaterhaz Apr 24 '17 at 11:01
not only perhaps, it really should be `Long`. Because it will *definitely* crash on row counts above `Integer` this is a serious issue not only "nice to have feature". Not using `Long` for row counts is very bad practice. – Pᴇʜ Apr 24 '17 at 11:06
I will explain it even that you deleted your comment: Interger in VBA is max `32767` and Excel rows are up to `1048576`. Which means your sub is working for the first `32767` and failing for `1015809` rows (so we can concur that this sub fails in more cases than it will work). This has nothing to do with whinging about. Integer is just the wrong type for row counts. – Pᴇʜ Apr 24 '17 at 11:27
If this was a serious project then sure, OP is asking for, what looks like, a list of emoyees and hours spent doing something. If there are more than 32,000 employees in this workbook then excel isn't what OP should be using. So calm down – Skaterhaz Apr 24 '17 at 11:29
1

Wouldn't it be much simpler to change those definitions to `As Long`? Don't take the remarks as personal, it's only about good practice. Always use `Long` by default unless there's a specific reason. :) – A.S.H Apr 24 '17 at 11:41
No need being rude to me. I didn't mention that issue to blame you or something. I just nicely said this because people learn from this site and learning from wrong answers isn't a good idea (other people might have another use case for this). Just because something might work this way in some cases doesn't make it correct. If I can crash a code without changing it, then it is just a bad code. It's just about attitude and good practice. – Pᴇʜ Apr 24 '17 at 11:42
How is this a wrong answer? I completely agree that Longs are safer than Integers, I'm not disputing that. At no point did the OP say this was a serious project that required tens of thousands of rows. Hence Integers are fine. – Skaterhaz Apr 24 '17 at 11:46
I can only repeat that this is a site where other people learn from too. So other people might have similar questions but with a different use case where they have more than 32767 rows and we will end up people asking questions like this [VBA Macro crashes after 32000 rows](http://stackoverflow.com/questions/10558540/vba-macro-crashes-after-32000-rows). The question is easy: "Is there any advantage using `integer` over using `long`?" no it isn't because there is just no need to limit this function to integer if you are able to use long. It's just bad practice. – Pᴇʜ Apr 24 '17 at 11:55

Excel Macro - Fetching the values of one column based on the values from other column

2 Answers2