empty cells to only appear after populated cells

%3CLINGO-SUB%20id%3D%22lingo-sub-1346392%22%20slang%3D%22en-US%22%3Eempty%20cells%20to%20only%20appear%20after%20populated%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1346392%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20away%20have%20empty%20cells%20to%20only%20appear%20after%20populated%20cells%20after%20of%20the%20Descending%20%22R%22%20sort%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20GreaterThan4k()%3CBR%20%2F%3ERange(%22S2%22).Value%20%3D%20Range(%22R2%22).Value%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3ERange(%22R2%2CT2%3AT27%2CU2%3AU11%2CV2%3AV5%2CU41%3AU3880%22).ClearContents%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20Application%3CBR%20%2F%3E.Calculation%20%3D%20xlCalculationAutomatic%3CBR%20%2F%3E.ScreenUpdating%20%3D%20True%3CBR%20%2F%3E.DisplayStatusBar%20%3D%20True%3CBR%20%2F%3E.EnableEvents%20%3D%20False%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22B41%3ABF81%22).Select%3CBR%20%2F%3EActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.CLEAR%3CBR%20%2F%3EActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.Add2%20_%3CBR%20%2F%3EKey%3A%3DRange(%22R41%3AR81%22)%2C%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3DxlDescending%2C%20DataOption%3A%3DxlSortNormal%3CBR%20%2F%3EWith%20ActiveWorkbook.Worksheets(%22Sheet1%22).Sort%3CBR%20%2F%3E.SetRange%20Range(%22B41%3ABF81%22)%3CBR%20%2F%3E.Header%20%3D%20xlGuess%3CBR%20%2F%3E.MatchCase%20%3D%20False%3CBR%20%2F%3E.Orientation%20%3D%20xlTopToBottom%3CBR%20%2F%3E.SortMethod%20%3D%20xlPinYin%3CBR%20%2F%3E.Apply%3CBR%20%2F%3EEnd%20With%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ERange(%22R2%22).Value%20%3D%20Range(%22S2%22).Value%3CBR%20%2F%3ERange(%22T2%22).Value%20%3D%20Range(%22R2%22).Value%3CBR%20%2F%3ERange(%22T3%22).Value%20%3D%20Range(%22Q10%22).Value%3CBR%20%2F%3ERange(%22AT41%3AAT48%22).Copy%3CBR%20%2F%3ERange(%22T4%22).PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3C%2FP%3E%3CP%3ERange(%22U2%22).Value%20%3D%20Range(%22Q19%22).Value%3CBR%20%2F%3ERange(%22U3%22).Value%20%3D%20Range(%22P10%22).Value%3CBR%20%2F%3ERange(%22R2%22).Value%20%3D%20Range(%22U2%22).Value%3C%2FP%3E%3CP%3ERange(%22AS41%3AAS42%22).Copy%3CBR%20%2F%3ERange(%22U4%22).PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%3C%2FP%3E%3CP%3ERange(%22R2%22).Select%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20Application%3CBR%20%2F%3E.ScreenUpdating%20%3D%20True%3CBR%20%2F%3E.DisplayStatusBar%20%3D%20True%3CBR%20%2F%3E.EnableEvents%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22BG14%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22BI11%22).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3Ea%20%3D%20MsgBox(%22TRADING%20INSTRUMENTS%20MUST%20DEMONSTRATE%22%20%26amp%3B%20_%3CBR%20%2F%3EvbCrLf%20%2B%20%22LOGARITHMICALLY%20NEGATIVE%20PEACHFUZZ%20VALUE.%22%20%26amp%3B%20_%3CBR%20%2F%3EvbCrLf%20%2B%20%22%22%20%26amp%3B%20_%3CBR%20%2F%3EvbCrLf%20%2B%20%221%26amp%3B3%20MINUTE%20FREESTOCKCHARTS.COM%20STATUS%20MUST%20GENERATE%22%20%26amp%3B%20_%3CBR%20%2F%3EvbCrLf%20%2B%20%22DOWNWARD%20BOUND%20PRICE%2C%20LESS%20THAN%20VWAP.%22%20%26amp%3B%20_%3CBR%20%2F%3EvbCrLf%20%2B%20%22%22%20%26amp%3B%20_%3CBR%20%2F%3EvbCrLf%20%2B%20%22DO%20YOU%20WANT%20TO%20EDIT%20HonorSystem24%20RESULTS%2C%20TO%20CONFIRM%22%20%26amp%3B%20_%3CBR%20%2F%3EvbCrLf%20%2B%20%22NEGATIVE%20PEACHFUZZ%2C%20AND%20FREESTOCKCHARTS.COM%20STATUS%3F%22%2C%20vbQuestion%20%2B%20vbYesNo%2C%20%22HonorSystem24%22)%3CBR%20%2F%3EIf%20a%20%3D%20vbYes%20Then%3CBR%20%2F%3ECall%20BFClear%3C%2FP%3E%3CP%3EElseIf%20a%20%3D%20vbNo%20Then%3C%2FP%3E%3CP%3EMsgBox%20%22YOU%20MAY%20HAVE%20UNALLOCATED%20FUNDS%20REMAINING.%20SEE%20'SUGGESTED'%20TO%20ADD%20ADDITIONAL%20SHARES%22%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1346392%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Frequent Contributor

Is there away have empty cells to only appear after populated cells after of the Descending "R" sort?

 

Sub GreaterThan4k()
Range("S2").Value = Range("R2").Value
Application.EnableEvents = False
Range("R2,T2:T27,U2:U11,V2:V5,U41:U3880").ClearContents

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayStatusBar = True
.EnableEvents = False
End With

Range("B41:BF81").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.CLEAR
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 _
Key:=Range("R41:R81"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B41:BF81")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


Range("R2").Value = Range("S2").Value
Range("T2").Value = Range("R2").Value
Range("T3").Value = Range("Q10").Value
Range("AT41:AT48").Copy
Range("T4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Range("U2").Value = Range("Q19").Value
Range("U3").Value = Range("P10").Value
Range("R2").Value = Range("U2").Value

Range("AS41:AS42").Copy
Range("U4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Range("R2").Select

With Application
.ScreenUpdating = True
.DisplayStatusBar = True
.EnableEvents = True

End With

Range("BG14").Select
Selection.Copy
Range("BI11").Select
ActiveSheet.Paste



a = MsgBox("TRADING INSTRUMENTS MUST DEMONSTRATE" & _
vbCrLf + "LOGARITHMICALLY NEGATIVE PEACHFUZZ VALUE." & _
vbCrLf + "" & _
vbCrLf + "1&3 MINUTE FREESTOCKCHARTS.COM STATUS MUST GENERATE" & _
vbCrLf + "DOWNWARD BOUND PRICE, LESS THAN VWAP." & _
vbCrLf + "" & _
vbCrLf + "DO YOU WANT TO EDIT HonorSystem24 RESULTS, TO CONFIRM" & _
vbCrLf + "NEGATIVE PEACHFUZZ, AND FREESTOCKCHARTS.COM STATUS?", vbQuestion + vbYesNo, "HonorSystem24")
If a = vbYes Then
Call BFClear

ElseIf a = vbNo Then

MsgBox "YOU MAY HAVE UNALLOCATED FUNDS REMAINING. SEE 'SUGGESTED' TO ADD ADDITIONAL SHARES"
End If


End Sub

0 Replies