Solved: Pulling Value Only (not equations)

[Erkd715]

I have a string of VBA code that is running perfectly, except i need the code to only pull the values. Right now it is pulling any cell that has an equation in it, even if the cell has no value.:dunno

Could anyone help me get this string of code to only look at fields with a populated value...?:)


Sub Summarize()
Dim ws As Worksheet
Dim lastRng As Range
Application.ScreenUpdating = False 'speed up code
Cells.Select
Selection.ClearContents
Range("A1").Select
For Each ws In ThisWorkbook.Worksheets
Set lastRng = ThisWorkbook.Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)
Select Case ws.Name
Case "Summary" 'exlude
'do nothing
Case Else
ws.Activate

Range("A2", Range("A65536").End(xlUp)).EntireRow.Copy lastRng

Application.CutCopyMode = False 'clear clipboard

End Select
Next
Sheets("Summary").Activate
Columns("A:A").Select
ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Add Key:=Range("A2:A14") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Summary").Sort
.SetRange Range("A2:J14")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True
End Sub




Thanks in Advance!!!:friends:

[Erkd715]

I think I need to add a .Value but I'm not sure where to add it..

[mdmackillop]

Instead of this

Range("A2", Range("A65536").End(xlUp)).EntireRow.Copy lastRng


You either want to filter the range to another location, or loop through each value, copying to the next available cell. Depends on the data size and question below.

"Cell has no value" is confusing. Is it 0 or an error result or an empty string?

[shrivallabha]

And Also:
Cells.Select
Selection.ClearContents
Range("A1").Select

As

Cells.ClearContents

[Erkd715]

I am pulling all of the data from 4 sheets and consolidating it into 1 sheet. On the 4 sheets I am pulling from, there are equations in all of the cells. My current program looks at all of these cells as if they are populated. I only want them to be looked at if the equation is used and outputs a value.

I can attach a copy of the file if that would help at all...


thanks,

[Erkd715]

In other words, when the equations are not being used, I don't want the program to look at those cells.

[mdmackillop]

I think a copy of the file is required.

[Erkd715]

Here is the excel file with the macro in place.

[mikerickson]

Perhaps this Case Else will work.

With ws.Range("A:A")

Range(.Cells(2, 1), .Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)).Copy Destination:=lastRange

End With

[Erkd715]

Perhaps this Case Else will work.

With ws.Range("A:A")

Range(.Cells(2, 1), .Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)).Copy Destination:=lastRange

End With


I am getting an error when I enter this into the code. Is there any particular place it would need to be entered? Or is there anything I would have to define before entering this?

[shrivallabha]

I've modified the code a bit
Sub Summarize()
Dim ws As Worksheet
Dim lastRng As Long
Application.ScreenUpdating = False 'speed up code

Cells.ClearContents

For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "Summary" 'exlude
'do nothing
Case Else
With ws
Name = ws.Name
lastRng = .Range("A1048576").End(xlUp).Row
.Range("A2:J" & lastRng).Copy
End With
Sheets("Summary").Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Select
Next
Application.CutCopyMode = False 'clear clipboard
Call Delete
Sheets("Summary").Activate
Columns("A:A").Select
ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Add Key:=Range("A2:A14") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Summary").Sort
.SetRange Range("A2:J14")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True
End Sub

Sub Delete()
LastRow = Sheets("Summary").Range("A1048576").End(xlUp).Row
For i = LastRow To 2 Step -1
If Len(Cells(i, 1).Value) = 0 Then
Rows(i).Delete
End If
Next
End Sub

See if does it for you!

[mikerickson]

You'd put it into this section of the OP code.

The idea is to use .Find (with search direction:= xlPrevious) to find the last non-empty, non-"" cell in a column, rather than .End(xlUp) will finds the last filled cell in a column, even if that cell is filled by a formula that evaluates to "".

For Each ws In ThisWorkbook.Worksheets
Set lastRng = ThisWorkbook.Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)
Select Case ws.Name
Case "Summary" 'exlude
'do nothing
Case Else
ws.Activate
With ws.Range("A:A")

Range(.Cells(2, 1), .Find(What:="*", After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:= xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False)).Copy Destination:=lastRange

End With

Application.CutCopyMode = False 'clear clipboard

End Select
Next

[Erkd715]

I got a string that finally works. Thanks for all your help!

[shrivallabha]

If you could post the one that works...!