|
to 12楼:这些操作都可以通过录制宏后结合实际情况修改.
1汇总表和各班表添加有数据区域内的表格线如何做到
'下面这段代码,可以加到Sub aallformat()的过程中,也就是说,可以达到对所有的班级数据都加上边框
irow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
icolum = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1
'(返回一个工作表中有内容的最后一个单元格的行号，不管在哪列。ActiveSheet.UsedRange.Row为返回有内容的第一个单元格的行号，前面的空行都不计。ActiveSheet.UsedRange.Rows.Count – 1为返回有内容的单元格总行数。)
'通过求有内容的行数和列数,我们可以知道,可能有几个班级里在空单元格里输入了空格或别的不可见的字符,空格也算有内容,想彻底删除不可见的内容的单元格,用删除单元格或删除整行或删除整列,有时只对那些单元格按下"DEL"不管用.
Range(Cells(1, 1), Cells(irow, icolum)).Select '全选有内容的区域
Selection.Borders(xlDiagonalDown).LineStyle = xlNone '下面是我录制的宏,可以照搬来用.可以看得出,宏自动加框结,它是分六下加,左右,上下,里面.
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft) '
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("A2").Select
2汇总表可以自动设置成有自动筛选功能吗?
完全能,因为汇总表就只有一张,就算不用VBA临时手工做,也不会费时间
当然,想做到VBA中也简单.可以通过录制宏来分析利用.
3如何可以添加表头
可以,但最好做到页眉页脚中去,不会破坏表格的整体结构.下面是我在别的程序中有利用页眉页脚做了一个表头,你可以参考.
ActiveSheet.PageSetup.PrintArea = "" '先取消打印区域,不管原先有没有设
With ActiveSheet.PageSetup置障碍 '页眉的内容分左中右,可以分别设置
.LeftHeader = "" & Chr(10) & "" & Chr(10) & "归档名称：" & ActiveSheet.Name & "系列"
.CenterHeader = "&""宋体,粗体""&18图 纸 目 录"
.RightHeader = ""
.LeftFooter = "&9注:目录中图纸均按图号顺序排放,查阅后请及时归档。"
.CenterFooter = ""
.RightFooter = "第 &P 页"
'这些是页边距,不要对这些数字叫真,想要多少数字,录一下宏就知道.
.LeftMargin = Application.InchesToPoints(0.393700787401575)
.RightMargin = Application.InchesToPoints(0.354330708661417)
.TopMargin = Application.InchesToPoints(1.18110236220472)
.BottomMargin = Application.InchesToPoints(0.826771653543307)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PaperSize = xlPaperA4
End With
另,欢迎有心人继续完成我未做的优化及加强使用功能的工作!!!
to18楼的哥们: [em04][em04][em04]能在这里多交几个朋友我很高兴,不过....,你还是别叫我"大哥",因为这辈子还没有去做变性手术的打算,呵呵
[此贴子已经被作者于2004-10-11 21:02:27编辑过] | 
楼主: rylane
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2004-10-11 10:32
楼主
