ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ½Ð°Ý¦p¦ó§âµL¸ê®Æªº¦h¾l­¶­±³]©w¤@«ö¶s§R°£

[µo°Ý] ½Ð°Ý¦p¦ó§âµL¸ê®Æªº¦h¾l­¶­±³]©w¤@«ö¶s§R°£

¦U¦ì«e½ú¦n~

¦pªþ¥[ÀɮסA¹w³]¦³61­¶(¦]¤W¶Ç¤£¤F¡A´î¦¨10­¶)¡A¦ý¦]¥Î³~¡A¦³®É¥Î¤£¤F¨º»ò¦h¡A
¦³¨S¦³VBA¯à¼g¦¨¡G­Y¨S¥Î¨ìªº­¶­±µL¸ê®Æ(¨S¥Î¨ìªº­¶­±)¡A«ö¶s´N¦Û°Ê§R°£ªº¤èªk¡H

¦]¬°¤@­Óworkbook±`¦³¦n´X­Ó sheets¡A¤@­Ósheet¬ù1.5MB, ¥|­Ósheet ´N6MB, ÀÉ®×¹ê¦b¤Ó¤j¤F¡K

¤Ï¹L¨Ó»¡¡A¦pªG§Ú¹w³]20­¶¡A¤£°÷¥Î®É¡A¯à§_¤]·s¼W¤@vba«ö¶s, ¤@«ö´N¯à¦bsheet³Ì¤U­±ª½±µ·s¼W¤@·s­¶¡A
¥B¥k¤è"¦Ç¦â°Ï°ì"ªºÁ`­p³sµ²¤]¯à²Î­p¨ì¡H

¦Ç¦â°Ï°ì

GREY.PNG
2016-10-3 14:31


½Ð¤j®aÀ°¦£¤@¤U¤F~~¯u¤ß·PÁÂ

WB.ver06.xlsm.zip (268.31 KB)

¤U¤È¬ðµM·Q¨ì¥H¤U¼gªk¡G
===========
Sub §R°£ªÅ¥Õ­¶­±()

For i = 3128 To 112 Step -52

    If Range("AH" & i) = "" Then
    Range("AH" & i).Offset(-7, 0).Rows("1:52").EntireRow.Select
    Selection.Delete Shift:=xlUp

    End If

Next

End Sub
====================
¦ý°õ¦æ«á¡AÁ`­¶¼Æ·|¶Ã±¼¡A¦p¹Ï¡G
white area.PNG
2016-10-3 20:03

Á`­¶¼Æ61­¶¡A¦ý¹ê»Ú¥u¦³¤G­¶

³s±µ­¶¼Æªº¦Ç¦â°Ï°ì

GREY.PNG
2016-10-3 20:04

³s±µ­¶¼Æªº¦Ç¦â°Ï°ì

À³¸Ó¬Ocell AT74­n§ï¤½¦¡¡A¦ý·Q¤£¥X¨Ó¥i¥H¥Î¤°»ò¨ç¼Æ¡C
¥u¦n¦A½Ð¤j«L­ÌÀ°À°¦£~~

TOP

©êºp¤j®a¡A³Q§Úé¨ì¡A¥Î³Ì²Âªº¤èªk¸Õ¥X¤F!
¦A¥[°Ý¡A¦pªG°õ¦æ«á¦³¨â­¶¡A·Q¥t¦s¦¨pdf¡A¦p¦óÂ^¨ú¤u§@ªí¦WºÙ¬°pdf ªº¦WºÙ?
³o¦¸§Ú¯uªº¤£·|¤F....

TOP

¦^´_ 3# simplehope

¼õ¨ì¥b©]¤TÂI¡AÁ`ºâ¸Õ¥X¨Ó¤F¡A·s¼W­¶­±«á¡AÁÙ¬O·|¦³­¶¼Æ¶Ã±¼ªº°ÝÃD¡C
ºCºC¬ã¨s¡A¤]ÁÂÁ¦³¶i¨Ó¬Ýªº¤j®a¡C

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2016-10-8 15:17 ½s¿è

¦^´_ 4# simplehope
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Sh As Worksheet, xCol As Integer, Rng As Range, i As Integer
  4.     For Each Sh In ActiveWorkbook.Sheets
  5.          If Sh.PageSetup.PrintArea <> "" Then
  6.             Set Rng = Nothing
  7.             With Sh
  8.                 xCol = .VPageBreaks(1).Location.Column
  9.                 For i = 1 To .HPageBreaks.Count
  10.                     If .HPageBreaks(i).Location.Range("F16") = "" Then Set Rng = .HPageBreaks(i).Location: Exit For
  11.                 Next
  12.                 If Not Rng Is Nothing Then
  13.                     .PageSetup.PrintArea = .Range("a1", .Cells(Rng.Offset(-1).Row, xCol)).Address
  14.                     .Range("a1", .Cells(Rng.Offset(-1).Row, xCol)).Select
  15.                     .Range(Rng, .Range("A" & .Cells.SpecialCells(xlCellTypeLastCell).Row)).Resize(, xCol).Delete xlUp
  16.                     'AJÄæ­ì¥»¤½¦¡=IF(AT14="","",$AT$74) , §ï¤½¦¡ =Á`­¶¼Æ
  17.                 End If
  18.             End With
  19.             Sh.Names.Add Name:="Á`­¶¼Æ", RefersToR1C1:=Sh.HPageBreaks.Count + 1
  20.         End If
  21.     Next
  22. End Sub
½Æ»s¥N½X
PS:2016/10/08 ­×¥¿
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

ÁÂÁÂG¤j¡A³o°÷¤p§Ì¬ã¨s¦n¤@°}¤l¤F¡G)
«Ü°ª¯Åªº¼gªk¡K¤p§Ìªº¤g¬¶¹ê¦bºF·\µLªk¬Û¤ñ


¸òwith ¡E¡E¡Eend with
¸òfor each...in
¸òif not ±ø¥ó¦¡ ¯uªº¬O«Ü¤£¼ô°Ú¡K

TOP

¦^´_ 5# GBKEE

G¤j¤£¦n·N«ä¡A°õ¦æ¨ì¤@¥b·|¥X²{¿ù»~¡G"°}¦C¯Á¤Þ¶W¥X½d³ò"¦p¤U¹Ï¡G
   
error.PNG
2016-10-8 09:20

error2

error2.PNG
2016-10-8 09:23

²q°ÝÃD¥X¦b xCol = .VPageBreaks(1).Location.Column  ¦ý¤£·|§ï....

¹ï¥H¤U³o¥y¤]¤£À´¡G
Range(Rng, .Range("A" & .Cells.SpecialCells(xlCellTypeLastCell).Row)).Resize(, xCol).Delete xlUp
'AJÄæ­ì¥»¤½¦¡=IF(AT14="","",$AT$74) , §ï¤½¦¡ =Á`­¶¼Æ

ÁÙ¦³³o¥y¬O
Sh.Names.Add Name:="Á`­¶¼Æ", RefersToR1C1:=Sh.HPageBreaks.Count + 1
·N«ä¬O·s¼W¤@­Ó¦WºÙ½d³ò¥s "Á`­¶¼Æ"¡A¨úR1C1®æ¦¡¡A¬Ý¦³´X­ÓHPageBreak ¦A+ 1 ¶Ü?
¨ºAJÄæ­n«ç»ò³s¨ì"Á`­¶¼Æ"©O?
¸£³U¦³ÂIÂण¹L¨Ó

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2016-10-8 15:31 ½s¿è

¦^´_ 7# simplehope

5# ªºµ{¦¡½X¤w­×¥¿,½Ð¦A¸Õ¸Õ

'** ¨úA1¨ì ²Ä¤@­Ó««ª½¤À­¶½uªº³Ì¥kÃ䪺Äæ¦ì)¦A¦V¤U¨ì¤u§@­¶ªº ³Ì«á¤@­ÓÀx¦s®æªº¦C¸¹******
Range(Rng, .Range("A" & .Cells.SpecialCells(xlCellTypeLastCell).Row)).Resize(, xCol).Delete xlUp
'AJÄæ­ì¥»¤½¦¡=IF(AT14="","",$AT$74) , §ï¤½¦¡ =Á`­¶¼Æ
'SpecialCells : expression.SpecialCells(Type, Value)
'SpecialCells ¤èªk    ¶Ç¦^ Range ª«¥ó¡A¦¹ª«¥ó¥Nªí»P«ü©w«¬ºA¤Î­È¬Û²Å¦Xªº©Ò¦³Àx¦s®æ¡CRange ª«¥ó
'Type     ¥²¿ïªº XlCellType¡C­n¥]§tªºÀx¦s®æ¡C
'xlCellTypeLastCell¡C¤w¥Î½d³òªº³Ì«á¤@­ÓÀx¦s®æ=>¤u§@­¶ªº ³Ì«á¤@­ÓÀx¦s®æ

ÁÙ¦³³o¥y¬O
Sh.Names.Add Name:="Á`­¶¼Æ", RefersToR1C1:=Sh.HPageBreaks.Count + 1
**·s¼W¤@­Ó¦WºÙ½d³ò¥s "Á`­¶¼Æ"¡A¨úR1C1®æ¦¡¡A¬Ý¦³´X­ÓHPageBreak ¦A+ 1 **
¨ºAJÄæ­n«ç»ò³s¨ì"Á`­¶¼Æ"©O?
**** ¤£¬O¦³½Ð§A ¦bAJÄæ­ì¥»¤½¦¡=IF(AT14="","",$AT$74) , §ï¤½¦¡ =Á`­¶¼Æ****
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¶×¥X¸ê®Æ¨ì·s¤u§@ªí¡A¦p¦ó¸Ñ¨M¦³ªÅ¥Õ¦C¡A¸ê®Æ¤£³s³e°ÝÃD

¥»©«³Ì«á¥Ñ simplehope ©ó 2016-10-8 18:52 ½s¿è

¤§«e¼g¤F­ÓVBA¡A°µ¶×¥X¨ì·s¤u§@ªí¥i¥H¦¨³s³e¸ê®Æ¡A
­ì¥»AÄæ(No.ªºÄæ¦ì)µL¤½¦¡¡A¦ý¦]¬°­n¦Û°Ê¬y¤ô¸¹»Ý¨D¡A§âAÄæ(No.ªºÄæ¦ì)®M¤½¦¡°µ¬y¤ô¸¹¤§«á¡A
¦p¤U¹Ï¥Ü
error3.PNG
2016-10-8 18:52


¦A¶×¥X«á´N¦³¸ê®Æ¤£³s³e°ÝÃD
¦p¤U¹Ï¥Ü

err2

error2.PNG
2016-10-8 18:52


¤p§Ì¾q¶w¹ê¦b¸Õ¤£¥X¤èªk¸Ñ¨M¡A¥u¦n¤S¨Ó½Ð¤j¤jÀ°À°¦£¤F~~ÁÂÁÂ
WB2.zip (790.07 KB)

TOP

¦^´_ 8# GBKEE


·PÁÂG¤j¡A¨ü±Ð¤F~~¥i¥H¥Î¤F!

TOP

        ÀR«ä¦Û¦b : ¥Í®ð¡A´N¬O®³§O¤Hªº¹L¿ù¨ÓÃg»@¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD