需要一些代码，以便根据D列中的最后一个值（在过滤后不是-100％）将打印区域更改为该代码

chandu 发表于 Dev

成都

我正在寻找一些代码，该代码根据D列中的最后一个值（设置为在过滤后不是-100％）来设置打印区域。看图片了解我的意思

目前，我的代码每个月都按D列对该数据进行排序，D列是此图像中的第四列。现在，我需要它将打印区域（蓝线）移到D列中不包含-100.0％的最后一行，在这种情况下，它是上面的行（第145行）。请让我知道如何将其自动化。

这是代码：

Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook, wb4 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet, ws5 As Worksheet, ws6 As Worksheet, ws7 As Worksheet
Dim lastRow As Long, i As Long
Dim value As String
Dim OldDate, NewDate As String
Dim ws As Worksheet

OldDate = Format(DateSerial(Year(Date), Month(Date) - 1, 0), "ddmmyy")
NewDate = Format(DateSerial(Year(Date), Month(Date), 0), "ddmmyy")


Set wb4 = Workbooks.Open("\\ldnfortunenet\fortunenetdocuments\MAC\IMA\Sector Analysis\Final\IMA Sector Analysis " & OldDate & ".xlsx", 0)
Set ws4 = wb4.Sheets("IMA 20-60")
Set ws5 = wb4.Sheets("IMA 40-85")
Set ws6 = wb4.Sheets("IMA Flexible")
Set ws7 = wb4.Sheets("Look Up Match")



ws4.ListObjects("Table33").Sort.SortFields. _
    Clear
ws4.ListObjects("Table33").Sort.SortFields. _
    Add Key:=Range("Table33[[#All],[YTD Perf]]"), SortOn:=xlSortOnValues, _
    Order:=xlDescending, DataOption:=xlSortNormal
With ws4.ListObjects("Table33").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

ws5.ListObjects("Table35").Sort.SortFields. _
    Clear
ws5.ListObjects("Table35").Sort.SortFields. _
    Add Key:=Range("Table35[[#All],[YTD Perf]]"), SortOn:=xlSortOnValues, _
    Order:=xlDescending, DataOption:=xlSortNormal
With ws5.ListObjects("Table35").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

ws6.ListObjects("Table3").Sort.SortFields _
    .Clear
ws6.ListObjects("Table3").Sort.SortFields _
    .Add Key:=Range("Table3[[#All],[YTD Perf]]"), SortOn:=xlSortOnValues, _
    Order:=xlDescending, DataOption:=xlSortNormal
With ws6.ListObjects("Table3").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

杰里米

像这样吗？

Sub Jeremys_Macro()
Dim RHS_Column As String

Range("XFD1").End(xlToLeft).Activate
RHS_Column = Mid(ActiveCell.Address, 2, WorksheetFunction.Find("$", Right(ActiveCell.Address, Len(ActiveCell.Address) - 1)) - 1) ' Find the last column

Range("D1048576").End(xlUp).Activate 'Finds the bottom row
Do Until ActiveCell.Value <> 1 And ActiveCell.Value <> -1 ' loops until the cell isn't 100% *OR* -100% (I didn't know if it was 100% or -100% - the screenshot suggests it's minus)
    ActiveCell.Offset(-1).Activate
Loop

ActiveSheet.PageSetup.PrintArea = ActiveSheet.Range("A1:" & RHS_Column & ActiveCell.Row).Address

End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。