I want to fill in the ruled line to value from value to value in the direction of increasing row in excel.

I want to fill in empty characters with excel

in the direction of increasing rows I want to fill in the ruled line to the value: that value
I want to fill in value to value: coloring
I want to fill from value to ruled line: that value

I'm looking for it.Thank you for your cooperation.

20200319 ----------------------------
I tried it.
Please tell me how to make it versatile.
①I want to remove maxr=50 fixation.
②There's only one line.I want to run it in all columns.

 Fill in characters from sub-rules to ruled lines()
    ic = 1
    maxr = 50
    moji=""
    Fori=1 To maxr
        If Cells(i,ic)<>"Then
             moji=Cells(i,ic)
         Else
             Cells(i,ic)=moji
         End If
         If Cells(i,ic).Borders(xlEdgeBottom).LineStyle=-4142 Then
         Else
           moji=""
         End If
    Next
    Fori=maxr To 2 Step-1                    
        If Cells(i,ic)<>"Then
            moji=Cells(i,ic)
         Else
            Cells(i,ic)=moji
          End If
          If Cells(i-1,ic).Borders(xlEdgeBottom).LineStyle=-4142 Then
          Else
             moji=""
          End If
      Next
end sub

vba

2022-09-30 16:19

1 Answers

I immediately thought about the that I want to remove the maxr=50 fixation.

I can't get the bottom line number of the ruled line, so I understand that maxr is fixed to 50. Is that correct?

It will take some time, but Use the getmaxrowkeisen function to get the bottom row with ruled lines and set it to maxr Is the method different from your request?

 Fill in characters from sub-rules to ruled lines()

    Dim maxRowKeisen As Double
    DimchkFlg As Boolean

    ic = 1
    maxr = getmaxrowkeisen (Rows.Count)
    moji=""
    Fori=1 To maxr
        If Cells(i,ic)<>"Then
             moji=Cells(i,ic)
         Else
             Cells(i,ic)=moji
         End If
         If Cells(i,ic).Borders(xlEdgeBottom).LineStyle=-4142 Then
         Else
           moji=""
         End If
    Next
    Fori=maxr To 2 Step-1
        If Cells(i,ic)<>"Then
            moji=Cells(i,ic)
         Else
            Cells(i,ic)=moji
          End If
          If Cells(i-1,ic).Borders(xlEdgeBottom).LineStyle=-4142 Then
          Else
             moji=""
          End If
      Next
End Sub

Function getmaxrowkeisen (maxRow As Double) As Double

  Do While chkFlg = False
    If Cells(maxRow, 1).Borders(xlEdgeBottom).LineStyle=-4142 Then
      maxRow = maxRow-1
    Else
      chkFlg = True
      getmaxrowkeisen=maxRow
    End If
  Loop

End Function



		
		
			

				

					
				

				
					2022-09-30 16:19

			
			If you have any answers or tips



		

	
		Popular Tags
	
	python x 4647
android x 1593
java x 1494
javascript x 1427
c x 927
c++ x 878
ruby-on-rails x 696
php x 692
python3 x 685
html x 656
	


	
		Popular Questions
	
	
	542 Unable to install versioned in Google Colab

	546 Understanding How to Configure Google API Key

	709 When building Fast API+Uvicorn environment with PyInstaller, console=False results in an error

	547 Who developed the "avformat-59.dll" that comes with FFmpeg?

	549 PHP ssh2_scp_send fails to send files as intended