Conditional select range in a macro
Posted by Leonardo on October 23, 2000 3:18 PM
i'm trying to make a macro that takes some rows and copy them to another location, but the problem is that those rows are results from a form worksheet, so i want the macro to copy only the rows that contain information, can anybody help me?
Posted by Celia on October 26, 2000 5:03 PM
Leonardo
The best way to do this depends upon circustances.
Here's one way that copies all the rows to the new location and then deletes the blank rows from the new location :-
Sub CopyRowsWithData()
Dim newLocation As Range, col As Range
Dim toCheck As Range, cell As Range, toDelete As Range
Set newLocation = Rows(10) 'Change the new location(Row10) to fit your requirements
Application.ScreenUpdating = False
Selection.EntireRow.Copy
newLocation.Select
ActiveSheet.Paste
Application.CutCopyMode = False
Set col = ActiveCell.EntireColumn
Set toCheck = Intersect(Selection, col)
For Each cell In toCheck
If Application.CountA(cell.EntireRow) = 0 Then
If toDelete Is Nothing Then
Set toDelete = cell
Else
Set toDelete = Union(toDelete, cell)
End If
End If
Next
toDelete.Delete
End Sub
