[ copy cell if it contains text ]
Data is transferred from a web-form to Excel. Not every cell receives inputs. There are many cells, it is time consuming to scan each cell looking for text.
How do I get the text automatically copied from sheet1 to sheet2. But I don't want the cells displayed in the same layout as the original sheet. I would like them to be grouped together, eliminating all of the empty cells in between. I would also like to grab the title from the row that contains the text.
I found this macro:
Sub CopyC() Dim SrchRng As Range, cel As Range Set SrchRng = Range("C1:C10") For Each cel In SrchRng If cel.Value <> "" Then cel.Offset(2, 1).Value = cel.Value End If Next cel
It grabs only cells that contain text, but it displays it in the exact same layout that it found it in. Any help would be appreciated and save me a lot of scan time in the future, thanks in advance :)
I guess this is what you are looking for:
Sub CopyNonBlankCells() Dim cel As Range, myRange As Range, CopyRange As Range Set myRange = Sheet1.Range("C1:C20") '---> give your range here For Each cel In myRange If Not IsEmpty(cel) Then If CopyRange Is Nothing Then Set CopyRange = cel Else Set CopyRange = Union(CopyRange, cel) End If End If Next cel CopyRange.Copy Sheet2.Range("C1") '---> enter desired range to paste copied range without blank cells End Sub
Above code will copy range
Got this from here.
EDIT: Following answer is based on your comment ________________________________________________________________________________
If you'll write something like below
Set myRange = Sheet1.Range("G:G") Set myRange = Sheet2.Range("G:G")
myRange will be first set to
Sheet1.Range("G:G") and then to
Sheet2.Range("G:G") that means current range that
myRange will have is
If you want to use multiple ranges you can go for
UNION function but there's a limitation that using UNION, you can combine different ranges but of only one sheet. And your requirement is to combine ranges from different sheets. To accomplish that I am adding a new worksheet and adding your
G:G ranges from all the sheets to it. Then after using newly added sheet I am deleting it.
Following code will give you the desired output in the sheet named
Sub CopyNonBlankCells() Dim cel As Range, myRange As Range, CopyRange As Range Dim wsCount As Integer, i As Integer Dim lastRow As Long, lastRowTemp As Long Dim tempSheet As Worksheet wsCount = Worksheets.Count '--->wsCount will give the number of Sheets in your workbook Set tempSheet = Worksheets.Add '--->new sheet added tempSheet.Move After:=Worksheets(wsCount + 1) For i = 1 To wsCount If Sheets(i).Name <> "Result" Then '---> not considering sheet "Result" for taking data lastRow = Sheets(i).Cells(Rows.Count, "G").End(xlUp).Row '--->will give last row in sheet lastRowTemp = tempSheet.Cells(Rows.Count, "G").End(xlUp).Row '--->will give last row in newly added sheet Sheets(i).Range("G1:G" & lastRow).Copy _ tempSheet.Range("G" & lastRowTemp + 1).End(xlUp)(2) End If Next i lastRowTemp = tempSheet.Cells(Rows.Count, "G").End(xlUp).Row Set myRange = tempSheet.Range("G1:G" & lastRowTemp) '--->setting range for removing blanks cells For Each cel In myRange If Not IsEmpty(cel) Then If CopyRange Is Nothing Then Set CopyRange = cel Else Set CopyRange = Union(CopyRange, cel) End If End If Next cel CopyRange.Copy Sheets("Result").Range("G1") '---> enter desired range to paste copied range without blank cells Application.DisplayAlerts = False tempSheet.Delete '--->deleting added sheet Application.DisplayAlerts = True End Sub
You can use arrays!
Instead of copying information from one cell to another, you can store all your information in an array first, then print the array on another sheet. You can tell the array to avoid empty cells. Typically, using arrays is the best way to store information. (Often the fastest way to work with info)
If you are only looking at one column, you could use a one-dimensional array. If you are looking at multiple columns, and want to print the information into the corresponding column (but different cells) in another page then you could a multi-dimensional array to store column number/anything else you wanted.
From your code, it could look like this:
Sub CopyC() Dim SrchRng As Range, cel As Range 'Declare your 1-d array (I don't know what you are storing) Dim myarray() as variant Dim n as integer Dim i as integer Set SrchRng = Range("C1:C10") 'define the number of elements in the array - 1 for now, increase it as we go n = 0 Redim myarray(0 to n) For Each cel In SrchRng If cel.Value <> "" Then 'redim preserve stores the previous values in the array as you redimension it Redim Preserve myarray(0 to n) myarray(n) = cel.Value 'increase n by 1 so next time the array will be 1 larger n = n + 1 End If Next cel 'information is now stored, print it out in a loop 'this will print it out in sheet 2 providing it is called "Sheet2" For i = 0 to ubound(myarray) Sheets("Sheet2").cells(i,1).value = myarray(i) Next i