Excel VBA, How to select rows based on data in a column?
Sub SelectAllReleventText()
Do While Range("A1").Offset(1, 6) <> Empty
Rows(ActiveCell.Row).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Here is my script, I was told that it does not do what it is intended to do, which I expected since this was my first try. I am coming up with a variable undefined error. I thought I had defined a variable, but I think it was not specific enough for Excel VBA.
This is what I am trying to do.
- Workbook 1, On B6 has an alphanumeric name, I want this row to be selected.
- Go down one line, if there is text, select that line.
- Continue until the text is no longer spread.
- Copy selected lines.
- Paste in another workbook (Workbook2) in bookmark 1 starting from line 2 as there are headings in line 1.
Thanks in advance. Just heads-up, I use Explicit options in my VBA because I was told that this is "the right way to do something" ...
source to share
Yes, use Option Explicit
is a good habit. Usage .Select
, however, is not the case: it reduces the speed of the code. Also fully justify the sheet names, otherwise the code will always execute for Activesheet
which may not be what you really wanted.
Is this what you are trying?
Option Explicit
Sub Sample()
Dim lastRow As Long, i As Long
Dim CopyRange As Range
'~~> Change Sheet1 to relevant sheet name
With Sheets("Sheet1")
lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To lastRow
If Len(Trim(.Range("A" & i).Value)) <> 0 Then
If CopyRange Is Nothing Then
Set CopyRange = .Rows(i)
Else
Set CopyRange = Union(CopyRange, .Rows(i))
End If
Else
Exit For
End If
Next
If Not CopyRange Is Nothing Then
'~~> Change Sheet2 to relevant sheet name
CopyRange.Copy Sheets("Sheet2").Rows(1)
End If
End With
End Sub
Note
If you have data from line 2 to line 10 and line 11 is empty and then you have data again from line 12, then the above code will only copy data from line 2 to line 10
If you want to copy all lines that have data then use this code.
Option Explicit
Sub Sample()
Dim lastRow As Long, i As Long
Dim CopyRange As Range
'~~> Change Sheet1 to relevant sheet name
With Sheets("Sheet1")
lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To lastRow
If Len(Trim(.Range("A" & i).Value)) <> 0 Then
If CopyRange Is Nothing Then
Set CopyRange = .Rows(i)
Else
Set CopyRange = Union(CopyRange, .Rows(i))
End If
End If
Next
If Not CopyRange Is Nothing Then
'~~> Change Sheet2 to relevant sheet name
CopyRange.Copy Sheets("Sheet2").Rows(1)
End If
End With
End Sub
Hope this is what you wanted?
Sid
source to share
The easiest way to do this is to use a method End
that gives you the cell you reach by pressing the end key and then the direction when you are in the cell (in this case, B6). This will not give you what you expect if B6 or B7 is empty.
Dim start_cell As Range
Set start_cell = Range("[Workbook1.xlsx]Sheet1!B6")
Range(start_cell, start_cell.End(xlDown)).Copy Range("[Workbook2.xlsx]Sheet1!A2")
If you cannot use End
, you will have to use a loop.
Dim start_cell As Range, end_cell As Range
Set start_cell = Range("[Workbook1.xlsx]Sheet1!B6")
Set end_cell = start_cell
Do Until IsEmpty(end_cell.Offset(1, 0))
Set end_cell = end_cell.Offset(1, 0)
Loop
Range(start_cell, end_cell).Copy Range("[Workbook2.xlsx]Sheet1!A2")
source to share